Counting records in a Query using VBA

[john.constantine]

I have looked through the help files but can't find anything -what I
want is the query equivalant to

CurrentDb.TableDefs("Mytable").RecordCount

Must be easy but has me stumped

 

[Jeanette Cunningham]

Hi john
Assuming the table has a primary key, you can go

Dim lngCount as Long
lngCount = DCount("*", "NameOfTable")

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Mr B]

John,

If you are wanting a quick count of the records, look into then DCount
function.

Dim varTmp
varTmp = DCount("IdFieldInTable", "TableName")

If there is some reason you need to actually us a query, post back and we
will try to help.

 

[Hans Up]

I have looked through the help files but can't find anything -what I
want is the query equivalant to

CurrentDb.TableDefs("Mytable").RecordCount

SELECT Count(*) AS num_records FROM Mytable;

 

[Roger Carlson]

You were close. The fastest way to get the record count of a table is:

CurrentDb.OpenRecordset("Mytable").RecordCount

The thing is, here it HAS to be a LOCAL table to return the correct record
count. If it is a linked table or a query, it will always return 1 as the
record count.

The fastest way to get the record count of a linked table is this:

Dim db As DAO.Database
Set db = OpenDatabase("c:\My Documents\LinkedDatabase.mdb")
db.OpenRecordset("Mytable").RecordCount

I suppose you could put it all on one like like this:
OpenDatabase("c:\My
Documents\LinkedDatabase.mdb").OpenRecordset("Mytable").RecordCount


On a table with over 7 million records, both of these returned the record
count in 1 second or less. Every other method mentioned will be an order of
magnitude slower. (Disclaimer: I didn't check ADO methods.)


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[UpRider]

John, use this function:

Function fcnCountQueryRecs(qQuery) As Long
fcnCountQueryRecs = DCount("*", qQuery)
End Function

qQuery is a string, the query name.

HTH, UpRider

 

[Jeanette Cunningham]

Bookmarked for future reference

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia