Doug Coats Doug Coats - 1 year ago 120
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


Debug.Print "Done!"
End Sub

Answer Source

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"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download