Doug Coats Doug Coats - 5 months ago 19
SQL Question

Pass VBA Variable into Access Query(Excel VBA)

Im new at trying to construct queries out of vba. I am trying to figure out how to pass a variable inside the VBA syntax. Mind showing me where im dumb?

I tried this below but there's an automation error that pops up. Ive noticed from playing aroudn that automation errors come up when youve just got syntax wrong, so hopefully its something small?

Any help is greatly appreciated

Sub GetDataFromAccess()
Dim cmd As New ADODB.Command, rs As ADODB.Recordset
Dim recordNum As Integer

recordNum = 7

cmd.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ashleysaurus\Desktop" & "\" & "xyzmanu3.accdb"
cmd.CommandType = adCmdText

cmd.CommandText = "SELECT * FROM Invoice WHERE OrderNumber <" & "'" & recordNum & "'" & "ORDER BY OrderNumber ASC"

Set rs = cmd.Execute
Sheet1.Range("A2").CopyFromRecordset rs

rs.Close
cmd.ActiveConnection.Close

Debug.Print "Done!"
End Sub

Answer

Assuming OrderNumber is a number, do not use quotes. Also make sure you have a space before Order By:

cmd.CommandText = "SELECT * FROM Invoice WHERE OrderNumber <" & recordNum & " ORDER BY OrderNumber ASC"
Comments