Hi All

I've wrote a module, which does record count for each table in database

Question: How can only count records from linked table?
currently it does count records for each table in database.


Option Compare Database

Sub Recordsintables()

Dim x As Integer
Dim count As Integer
Dim Name As String


Dim i As Integer
Dim tables As String
Dim r As New ADODB.Recordset

'Debug.Print "Total Table In Database include system Table: " & CurrentDb.TableDefs.count

For i = 0 To CurrentDb.TableDefs.count - 1

    DoEvents

    tables = CurrentDb.TableDefs(i).Name
   If Left(tables, 4) <> "dbo_" And Left(tables, 4) <> "bak_" And Left(tables, 4) <> "MSys" Then

            Debug.Print "Table Name: " & tables & " - Count: " & recordcount(tables)

        End If
Next i

End Sub

Function recordcount(tbl As String) As Long
Dim c As New ADODB.Command
c.ActiveConnection = CurrentProject.Connection
c.CommandText = "select count(*) from [" + tbl + "]"
Dim r As ADODB.Recordset
Set r = c.Execute
r.MoveFirst
recordcount = r.Fields(0).Value
r.Close
End Function