record count of table while looping through all tables

[Ben8765]

Hi, I am using the following code to loop through all the tables in my db.

While looping through the tables, I would like to get the record count of
each table.

At the ****, I'm using rs.RecordCount, but that is just returning the count
of the tables, not the count of the record in each table.

Do I need to do something like 'rs(rs!Name).record count'?

-Ben


------

Public Sub AppendTableRecords()

Dim strSelectAllTables As String

strSelectAllTables = "SELECT NAME FROM MSysObjects WHERE Type=1 AND Name not
like 'MSys%';"

Dim rs As New ADODB.Recordset
rs.Open strSelectAllTables, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

DoCmd.SetWarnings False

While Not rs.EOF

'******
MsgBox rs.RecordCount
'*******


'DoCmd.RunSQL "INSERT INTO [" & rs!Name & "] SELECT [" & rs!Name & "].* FROM
[" & rs!Name & "] IN '" & objDialog.FileName & "';"

rs.MoveNext
Wend
DoCmd.SetWarnings True

rs.Close

Set rs = Nothing

Close #iFileNo

End Sub

 

[Douglas J. Steele]

MsgBox DCount("*", rs!Name)