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.

I find it hard to believe that code would work as a sub since it should be: Set rs = New ADODB.RecordSet and not Set rs = New ADODB.Record — Rory
– Rory, Commented May 8, 2017 at 14:50
Typo in my tweaks is all, take it as given that I'm not lying when I say "I got it working as a Sub". — Dan Scally
– Dan Scally, Commented May 8, 2017 at 14:53
Well, if you correct that, it works as a function too - assuming the db path is correct and there are actually records matching the input criteria. — Rory
– Rory, Commented May 8, 2017 at 14:55
headdesk. Ah god I suck at life. Thank you - post as an answer and you will get reps :) — Dan Scally
– Dan Scally, Commented May 8, 2017 at 14:59

Rory · Accepted Answer · 2017-05-08 15:01:45Z

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.

answered May 8, 2017 at 15:01

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related