We help IT Professionals succeed at work.

ADO vs DAO... RecordCount

Mike Eghtebas
on
Medium Priority
1,093 Views
Last Modified: 2012-05-05
To ge number the of records in a table, I use:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Tabl1", dbOpenSnapshot)
If rst.RecordCount > 0 Then
    rst.MoveLast    '<-- this takes a long time (over 3,000,000 records)
    msgbox rst.RecordCount
End If
rst.Close

With ADO, however, we can have:

Dim rst 'As ADO....
Set rst = ...
msgbox rst.RecordCount
rst.Close

FYI: Table1 is in the current database not in a backend.

Could you please complete above few lines and make it work using ADO.

Thank you.




Comment
Watch Question

Dim rst As New ADODB.Recordset
rst.Open "Select * From MyTable", CurrentProject.Connection, adOpenKeyset
MsgBox rst.RecordCount

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
The key part being requesting either a adOpenStatic or adOpenKeyset.  The other options do not support the RecordCount property
CERTIFIED EXPERT
Top Expert 2010
Commented:
Hi Mike,

If all you want is a record count, then why not just do this:

Dim rst As DAO.Recordset
Dim RecCount As Long
Set rst = CurrentDb.OpenRecordset("SELECT Count(*) AS RecCount FROM Tabl1", dbOpenSnapshot)
RecCount = rst!RecCount
rst.Close


Regards,

Patrick
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
And, if memory serves, the only ones Access supports.
Leigh PurvisDatabase Developer
CERTIFIED EXPERT
Commented:
And if absolute current accuracy isn't vital then
Currentdb.TableDefs("Tabl1").RecordCount

Commented:
Or use the access function that does essentially that:

msgbox dcount("*","Tabl1")

You do have a PK index on that table, right?
Mike EghtebasDatabase and Application Developer

Author

Commented:
BillPowell,
You method looks good.  I will try it shortly.

Patrick,
I suspect Count(*) AS RecCount with over 3,000,000 records might be a bit slugish.  I will try if the solution from Bill fails.

LPurvis,
Currentdb.TableDefs("Tabl1").RecordCount looks very good.  It is lighting fast.  However, some of these are queries.  With a query, I tried:

Currentdb.QueryDefs("Query1").RecordCount

Which producess an error (method or data member not found, highlighting RecordCount).  What is the correct syntax for query if possible at all?

Thanks,

Mike



Mike EghtebasDatabase and Application Developer

Author

Commented:
dqmq,

No, I don't have index.  dcount("*","Tabl1"), I guess, would take much longer than any other solution.

Thanks,

Mike
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
The recordcount is a property of the tabledef - hence it's not guaranteed to be bang up to date.
But it is also why it's lightning fast.

If all you wanted was the recordcount of a query - I'd likely look to Patrick's first.
An index should make things better yes - but a full table scan needn't be the end of the world - and you're not pulling back all of the records.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.