[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1025
  • Last Modified:

ADO vs DAO... RecordCount

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.




0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
BillPowellCommented:
Dim rst As New ADODB.Recordset
rst.Open "Select * From MyTable", CurrentProject.Connection, adOpenKeyset
MsgBox rst.RecordCount
0
 
BillPowellCommented:
The key part being requesting either a adOpenStatic or adOpenKeyset.  The other options do not support the RecordCount property
0
 
Patrick MatthewsCommented:
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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Leigh PurvisDatabase DeveloperCommented:
And, if memory serves, the only ones Access supports.
0
 
Leigh PurvisDatabase DeveloperCommented:
And if absolute current accuracy isn't vital then
Currentdb.TableDefs("Tabl1").RecordCount
0
 
dqmqCommented:
Or use the access function that does essentially that:

msgbox dcount("*","Tabl1")

You do have a PK index on that table, right?
0
 
Mike EghtebasDatabase and Application DeveloperAuthor 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



0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
dqmq,

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

Thanks,

Mike
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now