How can I use ADO to query access db in Excel custom functions

Question

I am trying almost to replicate VLOOKUP functionality with a custom function to fetch data into an Excel Spreadsheet from an Access DB. Never done this before but my understanding is that this ought to work:

Function query(lookup_value)

    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sQRY As String
    Dim strFilePath As String

    strFilePath = "Z:\filepath\database.accdb"

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " & strFilePath & ";Jet OLEDB:Database Password=password;"
    cnn.Open

    sQRY = "SELECT TOP 1 Ethnic FROM central_reference_table WHERE LearnerCode = '" & lookup_value & "'"

    rs.Open sQRY, cnn

    query = rs.Fields(0).Value

    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing


End Function

The code runs, but returns the generic #VALUE error. I can use the same connection string in a Sub with Sheet1.Range("A1").CopyFromRecordSet and it'll operate as expected, dropping the first row from the db into my worksheet. So it must be the query = rs.Fields(0).Value that's the problem, but everything I can find online says that's the correct way to do it, so I'm at a loss.

Answer 1

If you correct this:

Set rs = New ADODB.Record

to this:

Set rs = New ADODB.RecordSet

then it will work as a function, provided that the db path is correct and there are actually records matching the input criteria. You should really add some error handling for that.