Alternative to DLookup 1

[THWatson]

I have read that domain aggregate functions, such as DLookup, are system resource hogs. Is there an alternative, perhaps in VBA?

I am using Access 2000.

Thanks.
Tom

 

[kraxmo]

Dear Tom,

You are correct about domain aggregate functions being system hogs. They are good for quick and dirty database operations, but they are not very efficient. Using VBA, you can write your own database lookup and modifications routines that will do exactly what you want.

Here is an example of a VBA ADO DLookup-equivalent function:

Function gDomainLookup(astrColumn As String, astrTable As String, Optional astrWHERE As String = "") As Variant
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    
    On Error GoTo gDomainLookup_Error
    
    gDomainLookup = Null
    
    Set rs = New ADODB.Recordset
    
    strSQL = "SELECT [" & Trim(astrColumn) & "] FROM [" & Trim(astrTable) & "]"
    If Len(Trim(astrWHERE)) Then
        strSQL = strSQL & " WHERE " & Trim(astrWHERE)
    End If
    
    rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    gDomainLookup = rs.Fields(0).Value
    
gDomainLookup_Exit:
    If rs.State Then
        rs.Close
    End If
    
    Set rs = Nothing
    Exit Function
    
gDomainLookup_Error:
    MsgBox Err & ":" & Err.Description, vbExclamation, "gDomainLookup ERROR"
    DoEvents
    gDomainLookup = "* ERROR *"
    Resume gDomainLookup_Exit
End Function

To try this out, do the following:
1. Create a new Module (Module tab, New...)
2. In the Microsoft Visual Basic window, set the references:
a. Click the module's Tool menu, References...
b. Find Microsoft ActiveX Data Objects 2.0 (or greater) Library in the list and click its checkbox.
c. Click the OK button.
3. Copy/paste the above function from Function to End Function
4. Test it by:
a. Pressing Ctrl-G (Immediate window)
b. Typing ? gDomainLookup("column", "table&quot where column is the column name and table is table/query name.
c. Press the Enter key.
5. You can specify an optional WHERE clause (3rd parameter astrWHERE) just like the DLookup function.

Notes:
1. This function only returns the first occurrence of the data (if there are 5 table rows found, only the first will be returned)
2. If you need to talk to other databases, you will need to use an ADO Connection object instead of CurrentProject.Connection in the ADO Recordset Open method.

Check out ADO (ActiveX Data Objects) in the Access Help File for additional help and explanations.

Good Luck!!! Jim Kraxberger
Developing Access solutions since 1995

 

[THWatson]

Jim
Thanks for your lengthy and helpful reply. I will try out what you have suggested. I am just attempting to learn VBA so am not up to snuff with it yet, but will see where I get.
Tom