record count of table while looping through all tables



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'?



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 & "';"

DoCmd.SetWarnings True


Set rs = Nothing

Close #iFileNo

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question