troubleshooting Question

Opening DAO Recordset dbSnapshot vs dbForwardOnly

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft Access
11 Comments1 Solution1558 ViewsLast Modified:
I have a number of routines in a Access 2003 application I am working on that are purely for data retrieval.  No data editing or revision and the records are read in the order returned.  

The structure and function of these routines is all the same.  They are called interactively from another routine to pull information from a specific record in either a table or query.  In most cases only the first record of the dataset is read to get the information required.  If the dataset is EOF, the information was not found.

Since I'm making changes anyway I want to code these type of routines in the most efficient way.

In these routines, I noticed some cases the developer used
set rs = db.Openrecordset (selectstring, dbOpenSnapshot, dbReadonly)  

and in others the developer uses

set rs = db.Openrecordset (selectstring, dbOpenForwardOnly, dbReadonly)  

My question is:  In this scenario and type of usage which of these options (dbOpenSnapshot or dbOpenForwardOnly) is the most efficient, or is there any real difference?

Here's an example of this type of routine.
NOTE: In the example below I realize that MoveLast and MoveFirst would need to be removed if dbForwardOnly is used but that is easily done and the recordcount will always return 1.  I think those statements are remnants of the original ADO recordset handling.

Public Sub getInstallPlanInfoFromPropertyID(passedPropertyID As Long, _
                                               returnPAY_AGREE_TYPE As String, _
                                               returnPAY_AGREE_START_DATE As Date, _
                                               returnPAY_AGREE_END_DATE As Date, _
                                               returnPAYMENT_AGREEMENT_TYPE As String)

returnPAY_AGREE_TYPE = cNoRecFound
Dim wkPaidToDate As Double
                                                                        " From qryInstallPay_Main_ForCOPDW " & _
                                                                        " Where ([PropertyID] = " & passedPropertyID & " ) "
Set db = getCurrentDbC
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset(selectString, dbOpenSnapshot, dbReadOnly)  
If rs.EOF Then
    If rs.RecordCount > 0 Then
        returnPAY_AGREE_TYPE = Nz(rs!PAY_AGREE_TYPE, 0)
        '////old logic below, revised 09/27/16
        'returnPAY_AGREE_END_DATE = Nz(rs!PAY_AGREE_END_DATE, 0)
        ' new logic 09/27/16
        returnPAY_AGREE_START_DATE = Nz(rs!PAY_AGREE_START_DATE, cNoDateFound)    ' return cNoDateFound if Null   09/27/16
        returnPAY_AGREE_END_DATE = Nz(rs!PAY_AGREE_END_DATE, cNoDateFound)        ' return cNoDateFound if Null   09/27/16
    End If
End If
Set rs = Nothing

End Sub
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros