record count of table while looping through all tables

B

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
 
J

J_Goddard via AccessMonster.com

Hi -
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.

Yes, that's right. rs.RecordCount returns the numbers of items in the
recordset rs, which is a list of your tables.

To do what you want, try using DCount:

msgbox dCount("*",rs!Name)

or better, maybe:

debug.print "Table " & rs!name & " record count: " & dCount("*",rs!Name)
(This allows the procedure to run without stopping all the time with a
message box)

HTH

John

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
 

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

Similar Threads


Top