ADO Count records in existing query

[Domski74]

Hi All,

I've been using DCount to count records in an existing query, however I was finding this to bwe a very slow function and thought I could speed it up using ADO, my code is as below:

Function GetRecordCount(TableName As String) As Long
    Dim rs As New ADODB.Recordset
    Dim ssql As String
    Dim Result As Long
    ssql = "Select Count(*) From " & TableName
    rs.Open ssql, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Result = rs.Fields(0).Value
    rs.Close
    Set rs = Nothing
 
    GetRecordCount = Result
End Function

The problem is that this gives counts for tables but feeding it query names results in an error "No value given for one or more reuired parameters".

Please can anyone point me at a way of fixing this?

Thanks

 

[KenHigg]

I'm trying to imagine a scenario where a dcount without critria would be too slow to be of unacceptable - ? Can you explain how / where you would use this function?

 

[Domski74]

"DCount is an 'domain aggregate' function and is slow"

 

[Rabbie]

The domain aggregate functions are slow when used in a query that accesses large tables because they access the whole dataset for every row so this makes them slow.

A single call of Dcount should not be excessively slow.

 

[Domski74]

I was using Dcount to count the rows of a query containing multiple very large tables, so I guess that's why it's slow.

Can anyone help as to why my code falls down when I try to get a count of a query rather than a table?

thanks.

 

[KenHigg]

You should be able to write a sql statement that will do the same. I am aware that "DCount is an 'domain aggregate' function and is slow" that is why I ask how you were using it so you can spend your time attacking it from another angle...

 

[LPurvis]

Just to address the DCount debate briefly first (well... briefly-ish).

The Domain aggregates vary in performance and behaviour.
To count entire table data on local tables there is almost nothing faster. (Almost - however CurrentDb("TableName").RecordCount will be the fastest).
Even recordsets with efficient SQL statements don't beat it.
They lose ground however on linked tables. And more and more so (recursively called in queries - not a great idea!)

Given that the count is slow on a monolith of a query, it doesn't necessarily follow that a recordset will be substantially quicker.
I'd suggest looking at the query itself. (Have you executed "Select Count(*) From BadQueryName" in a query window?)

Finally - on to your technical issue.
One of your referenced queries is likely including a form reference.
A standard DAO function to resolve the call into opening a recordset and an ADO specific similar implementation are suggested here - which shouldn't be difficult to implement. (Note that refering to the parameters collection in a Jet based query isn't the hit that doing so to a server pased SP would be

Cheers.