dcount or DAO.recordset?

[mshakeelattari]

In MS Access VBA, I want to see whether a record exist or not. During googling, two suggestions were found. One is using DAO and the other is using dcount or dlookup. Is there any suggestion which one is the best option please?
Regards

[PhilOfWalton]

IMO the recordset approach is faster, because it uses indexes ... but for for a few Dlookups you won't notice any difference. If you are looping a number of DLookups in linked tables, you might spot a difference.
One advantage of a DLookup is that if you have a query that is not updateable, you can often get over the problem by removing the table that is causing the poblem and using a DLookup instead.

Out of interest, I attach some code written by Allen Brown, which simulated DLookup, bur appears faster.

Public Function ELookup(Expr As String, Domain As String, Optional Criteria As Variant, _
    Optional ORDERClause As Variant) As Variant
 
    On Error GoTo Err_ELookup
    'Purpose:   Faster and more flexible replacement for DLookup()
    'Arguments: Same as DLookup, with additional Order By option.
    'Return:    Value of the Expr if found, else Null.
    '           Delimited list for multi-value field.
    'Author:    Allen Browne. [e-mail]
    'Updated:   December 2006, to handle multi-value fields (Access 2007.)
    'Examples:
    '           1. To find the last value, include DESC in the OrderClause, e.g.:
    '               ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
    '           2. To find the lowest non-null value of a field, use the Criteria, e.g.:
    '               ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
    'Note:      Requires a reference to the DAO library.
    Dim MyDb As DAO.Database          'This database.
    Dim Rs As DAO.Recordset         'To retrieve the value to find.
    Dim rsMVF As DAO.Recordset      'Child recordset to use for multi-value fields.
    Dim varResult As Variant        'Return value for function.
    Dim strSql As String            'SQL statement.
    Dim strOut As String            'Output string to build up (multi-value field.)
    Dim lngLen As Long              'Length of string.
    Const strcSep = ","             'Separator between items in multi-value list.
 
    'Initialize to null.
    varResult = Null
 
    'Build the SQL string.
    strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strSql = strSql & " WHERE " & Criteria
    End If
    If Not IsMissing(ORDERClause) Then
        strSql = strSql & " ORDER BY " & ORDERClause
    End If
    strSql = strSql & ";"
 
    'Lookup the value.
    Set MyDb = CurrentDb
 
    Set Rs = MyDb.OpenRecordset(strSql, dbOpenForwardOnly)
    If Rs.RecordCount > 0 Then
        'Will be an object if multi-value field.
        If VarType(Rs(0)) = vbObject Then
            Set rsMVF = Rs(0).value
            Do While Not rsMVF.EOF
                If Rs(0).Type = 101 Then        'dbAttachment
                    strOut = strOut & rsMVF!filename & strcSep
                Else
                    strOut = strOut & rsMVF![value].value & strcSep
                End If
                rsMVF.MoveNext
            Loop
            'Remove trailing separator.
            lngLen = Len(strOut) - Len(strcSep)
            If lngLen > 0& Then
                varResult = Left(strOut, lngLen)
            End If
            Set rsMVF = Nothing
        Else
            'Not a multi-value field: just return the value.
            varResult = Rs(0)
        End If
    End If
    Rs.Close
 
    'Assign the return value.
    ELookup = varResult
 
Exit_ELookup:
    Set Rs = Nothing
    Set MyDb = Nothing
    Exit Function
 
Err_ELookup:
    Dialog.RichBox "Error " & Err & " " & Err.Description, vbExclamation, "ELookup Error " & Err.Number
    Resume Exit_ELookup
 
End Function
 
 

Phil

[TheSmileyCoder]

If you are just looking to see if a SINGLE record exists, you will be hard pressed to find any NOTICEABLE difference in performance.

In that case, I would use Dlookup, simply because its a one-line statement, i.e. less code to maintain, and document.
If you find your Dlookup is slow, then it is likely that your index have not been defined properly for the use case scenario.

Now if you are looking up many records, its a quite different matter.

[NeoPa]

Phil:
IMO the recordset approach is faster, because it uses indexes

I believe DLookup() also uses indexes Phil.

I have a table of over 1.6 million records in SQL Server. When I open it from Access and go to the last record it takes just over two seconds, every time (So not affected by cacheing).

When I do a DLookup() for the last known record it takes no discernable time. Even more telling, when I request a record that doesn't exist it also returns the Null value immediately.

This clearly means that DLookup() uses indexes, just as one would expect.

As Smiley indicates, there are many situations where using DLookup() can be a massive performance hit. These are generally when trying to use it from within SQL though. One shouldn't say to avoid that at all costs, as Phil introduced a scenario where it can be helpful, but certainly treat with extreme caution.

[zmbd]

I have found the same thing as TheSmileyCoder and NeoPa when using either DLOOKUP() or DAO.Recordset in the little databases I typically use.

My general rule(s) of thumb is this:
+ I avoid D-Functions in queries/SQL whenever possible as there is a slight performance hit as TheSmileyCoder points out in my smaller databases - I can only imagine that such hits would be worse on very large databases.
+ If I already have a recordset open, or will need that recordset later in the procedure then I use/open that recordset for the find/seek methods. (I often have recordsets open at a global level because more than one procedure will use it; however, that's another story :) )
+ If I do not have a recordset open then I tend to use the D-Functions.
Sort of the same phlosphy that Mr. Browne appears to express here (the highlight is mine :) ):

Allen Browne Getting a value from a table: DLookup()(read more)

Sooner or later, you will need to retrieve a value stored in a table. If you regularly make write invoices to companies, you will have a Company table that contains all the company's details including a CompanyID field, and a Contract table that stores just the CompanyID to look up those details. Sometimes you can base your form or report on a query that contains all the additional tables. Other times, DLookup() will be a life-saver.