How to get the total number of records in a table?

[Michel Jr]

Hi,

How can I get the number of records inside an access table?
I think I should use MoveLast, but I don't remember the correct way to do that...

Thanks for any help...

Michel Jr.

[BruceG]

The following should work:

Dim dbMyDB As Database
dim lngNbrOfRecs As Long

Set dbMyDB = OpenDatabase("C:\Whatever\MyData.mdb")
lngNbrOfRecs = dbMyDB.TableDefs("MyTable").RecordCount

Under DAO, when you access a table as a "table", the RecordCount property reflects the actual number of records in the table. The "MoveLast" thing applies when you open a record as a "dynaset" (which happens for example when you use OpenRecordset with a SQL statement). For example:

Dim dbMyDB As Database
dim rsMyRs As Recordset
dim lngNbrOfRecs As Long

Set dbMyDB = OpenDatabase("C:\Whatever\MyData.mdb")
Set rsMyRs = dbMyDB.OpenRecordset("SELECT blah blah", dbOpenDynaset)
' the following will only return 1 regardless of how
' many records are returned (or 0 if empty):
lngNbrOfRecs = rsMyRs.RecordCount
' use MoveLast to get the full count:
rsMyRs.MoveLast
' now recordcount will be good:
lngNbrOfRecs = rsMyRs.RecordCount

[/code]