Run-time error '3075'

[Middlemarch]

Having a problem with this connecting to Access

Function Dlookup(Field, Domain, Criteria) As String
    Dim r As DAO.Recordset
    Dim sql As String
    sql = "Select " & Field & " from " & Domain & " Where " & Criteria
    With MyCurrentDB.CreateQueryDef("", sql)
        Set r = .OpenRecordset
            If r.RecordCount > 0 Then
                Dlookup = r(0)
                .Close
            End If
        Set r = Nothing
    End With
End Function


Function MyCurrentDB() As DAO.Database
    Set MyCurrentDB = DAO.OpenDatabase("C:\DB\Test.mdb")
End Function

I use it in VB6 and just ported it into Excel but get
Run-time error '3075':


Application-defined or object-defined error
I suspect References and have Microsoft DAO 3.6 Object Library but do I need something else?
Or have mucked something in the code ?

Thanks

[Micron]

I thought Microsoft Office ##.# Object Library might be required in Excel for this but apparently it is not. If you are running the second code first, be aware that With MyCurrentDb will probably call it a second time. Probably not your issue though. Maybe you selected the library by clicking on the line in the dialog but forgot to check the box?

[CJ_London]

What line creates the error?

[davegri]

Function Dlookup(field, domain,criteria) is trying to create a UDF.
The Dlookup function already exists in Access. Try changing the UDF name.

[Micron]

Function Dlookup(field, domain,criteria) is trying to create a UDF.

That code works for me as long as I have a reference to DAO (and a valid file path), so while it's not good, it's not the problem if the code is in Excel. That's not entirely clear but it's how I interpret the op.

[CJ_London]

sql = "Select " & Field & " from " & Domain & " Where " & Criteria

Hope you don't use spaces or non alpha numeric characters in your database table and field names, otherwise this sql will fail

[Micron]

WRT spaces it will work if the table name is enclosed in brackets so I suppose the field or criteria would work with spaces also. Using a posted db from another thread:

Dlookup"PaymentMethodID","[Payment Methods]","PaymentMethod = 'Quit'"
7