Counting Records in recordset

  • Thread starter Thread starter Douglas
  • Start date Start date
D

Douglas

I have a form that can potentially open 1,500,000 records
if the user wants to. I do not want this and want a
message returned if their selection returns more than
500,000 records.

Currently I am doing something like this and is too slow:

vRecordCount = DCount("*","Table","Period = # AND Account
= # AND Company = #")
If vRecordCount > 500000 Then
MsgBox "Too many records. Make Criteria more specific."
Exit Sub
Endif

Is there a way to get a faster record count on a query of
record criteria of a table that can be used in conjunction
with opening the form.


Thanks for your help.

Douglas.
 
Douglas said:
I have a form that can potentially open 1,500,000 records
if the user wants to. I do not want this and want a
message returned if their selection returns more than
500,000 records.

Currently I am doing something like this and is too slow:

vRecordCount = DCount("*","Table","Period = # AND Account
= # AND Company = #")
If vRecordCount > 500000 Then
MsgBox "Too many records. Make Criteria more specific."
Exit Sub
Endif

Is there a way to get a faster record count on a query of
record criteria of a table that can be used in conjunction
with opening the form.


Thanks for your help.

Douglas.
Click to expand...


Probably if you use DAO and Recordset it may be faster...!

Public Function retCount(strSQL as string) as long
Dim rs as DAO.Recordset
Set rs=Currentdb.Openrecordset(strSQL)
If not rs.bof and rs.eof then
rs.movelast
retcount=rs.recordcount
else
retcount=0
end if
rs.close
set rs=Nothing
End Function


You may call it like this:
RecNum=retCount "SELECT * From Table WHERE Period ........."

Try it because it's really faster than DCOUNT....!

Alessandro(IT)
 
ALESSANDRO Baraldi said:
Probably if you use DAO and Recordset it may be faster...!

Public Function retCount(strSQL as string) as long
Dim rs as DAO.Recordset
Set rs=Currentdb.Openrecordset(strSQL)
If not rs.bof and rs.eof then
rs.movelast
retcount=rs.recordcount
else
retcount=0
end if
rs.close
set rs=Nothing
End Function


You may call it like this:
RecNum=retCount "SELECT * From Table WHERE Period ........."

Try it because it's really faster than DCOUNT....!
Click to expand...

First of call, that needs to be

RecNum=retCount("SELECT * From Table WHERE Period .........")

(Function arguments must be enclosed in parentheses)

I would think, though, that it might be faster to do a SELECT COUNT(*) As
TotalRecords..., rather than returning the entire recordset and having to go
to the end of it to get a complete count.

Public Function retCount(TableName As String, WhereClause As String) As Long
Dim rs as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT Count(*) As TotalRecords " & _
"FROM [" & TableName & "] " & _
"WHERE " & WhereClause
Set rs=CurrentDb.OpenRecordset(strSQL)
retCount=rs!TotalRecords
rs.close
set rs=Nothing
End Function

Use this retCount the same as DCount (except that you don't need the first
parameter):

vRecordCount = retCount("Table","Period = # AND Account = # AND Company =
#")
 
Back
Top