Recordset Resources

[treva26]

I have some code that counts the number of records an SQL statement returns.
It works fine, I am just wondering, is the recordset being left open and eating up resources?
Will it be closed when the VBA Sub ends? Or when the form is closed?

SQL = "SELECT COUNT(*) FROM QuotesQueryLimited WHERE [Quote Stage]='ECL - Saved';"

MsgBox CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly, dbSeeChanges).Fields(0).Value

If so I could do it this way:

 SQL = "SELECT COUNT(*) FROM QuotesQueryLimited WHERE [Quote Stage]='ECL - Saved';"

    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly, dbSeeChanges)
    MsgBox rst.Fields(0).Value 
    rst.Close
    Set rst = Nothing

Or is there a better version of the first way?

Thanks.

 

[HiTechCoach]

Have you tried using DCount()?

You will have to test to see which method performs the fastest.

 

[treva26]

I had read that Dcount was very slow, and since I am moving to SQL Server and having more users I thought I had better change all my Dcount's and Dlookup's to more efficient methods.

However having taken your advice, while using the SQL Server (with linked tables via ODBC), it seems Dcount is in fact slightly faster!

For example doing 200 counts in a very large table:
using either of the above methods = 2.1 seconds
Dcount = 1.9 seconds

Dlookup was also faster:
using either of the above methods = 0.6 seconds
Dlookup = 0.4 seconds

Unless there is a mistake in my test code?

Public Sub speedtest4()
SQL = "SELECT [Sales Rep] FROM Customers WHERE [Company Name]='SIAM GLOBAL ENGINEERING';"

t1 = Timer

'method 1
For x = 1 To 200
QN = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly, dbSeeChanges).Fields(0).Value
Next x

a1 = t1 - Timer
t1 = Timer

'method 2
For x = 1 To 200
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly, dbSeeChanges)
QN = rst.Fields(0).Value
    rst.Close
    Set rst = Nothing
Next x

a2 = t1 - Timer
t1 = Timer

'method 3
For x = 1 To 200
QN = DLookup("[Sales Rep]", "Customers", "'SIAM GLOBAL ENGINEERING'=[Company Name]")
Next x

a3 = t1 - Timer

MsgBox a1 & " , " & a2 & " , " & a3

End Sub




Public Sub speedtest3()
SQL = "SELECT COUNT(*) FROM Quotes WHERE [Quote Stage]='ECL - Saved';"

t1 = Timer

'method 1
For x = 1 To 200
QN = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly, dbSeeChanges).Fields(0).Value
Next x

a1 = t1 - Timer
t1 = Timer

'method 2
For x = 1 To 200
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly, dbSeeChanges)
QN = rst.Fields(0).Value
    rst.Close
    Set rst = Nothing
Next x

a2 = t1 - Timer
t1 = Timer

'method 3
For x = 1 To 200
QN = DCount("[Quote Stage]", "Quotes", "[Quote Stage]='ECL - Saved'")
Next x

a3 = t1 - Timer

MsgBox a1 & " , " & a2 & " , " & a3

End Sub

 

[treva26]

Oh and by the way, to answer my own original question:

After running hundreds of the short version, it would appear that it doesn't leave the recordset open or at least hasn't caused any slowdown or crashes on my computer.

 

[treva26]

In another test, I got a recordset cycled through it with .movenext looking for 5 matches.
Also with one version using .findfirst
Then 5 seperate dlookups

Once again the dlookups were faster:
200 loops of 1 recordset, looked through with .movenext for 5 values = 2.5 seconds
200 loops of 1 recordset, searched with .findfirst for 5 values = 2.5 seconds
200 loops of 5 dlookups = 2.1 seconds

BUT when searching for 10 values, the recordsets were quicker:
200 loops of 1 recordset, looked through with .movenext for 10 values = 3.7 seconds
200 loops of 1 recordset, searched with .findfirst for 10 values = 3.4 seconds
200 loops of 10 dlookups = 4.2 seconds