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) xlSheet.Range("G5").ClearContents '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 Else rs.MoveLast recCount = rs.RecordCount rs.MoveFirst 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.