(Solved) – VBA to Query Microsoft Access


I’m trying to extract information from a Microsoft Access database and put it into my Excel spreadsheet. I got the macro I wrote to return the correct value if I manually type in the lookup value, but when I try make it a dynamic reference so I can loop through a column of values, it’s giving me an error.

Here’s what I’ve got so far:

Sub Test()
Const DbLoc As String = "D:Usersmax.herringDocumentsDatabase3.accdb"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim recCount As Long
Dim SQL As String
Dim i As Long
Dim variable As String

'Setup References to Workbook and Sheet
Set xlBook = ActiveWorkbook
Set xlSheet = xlBook.Worksheets(1)


'Communicate to the user
Application.StatusBar = "Connecting to an external database..."
Application.Cursor = xlWait

Set db = OpenDatabase(DbLoc)

'For i = 1 To 2
variable = Range("A1")
SQL = "SELECT qx "
SQL = SQL & "FROM Table1 "
SQL = SQL & "WHERE Key = " & "'" & [variable] & "'"
'SQL = SQL & "WHERE Key = " & "'ANBMaleNS21216'"

'Execute Query and Populate the Recordset
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

'Copy Recordset to Spreadsheet
Application.StatusBar = "Writing to spreadsheet..."
If rs.RecordCount = 0 Then
    MsgBox "No data retrieved from database", vbInformation   vbOKOnly, "No Data"
    'GoTo SubExit
    recCount = rs.RecordCount
End If

xlSheet.Range("G5").CopyFromRecordset rs
End Sub

So it works if I replace

SQL = SQL & "WHERE Key = " & "'" & [variable] & "'"


SQL = SQL & "WHERE Key = " & "'ANBMaleNS21216'"

but I want to be able to say something like

For i = 1 to 120
variable = Sheet1.Cells(i, 1)
SQL = "SELECT qx "
SQL = SQL & "FROM Table1 "
SQL = SQL & "WHERE Key = " & "'" & [variable] & "'"

Does anyone know why this isn’t working? It seems like it has something to do with the quotation marks? If it helps, the field I’m using as the ‘Key’ in Microsoft Access is a short text, and the qx is a number.

Leave a Reply

Your email address will not be published. Required fields are marked *