Pass VBA Variable into Access Query(Excel VBA)

Question

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

do you put single quotes around numbers? ...er < " & recordNum & " ORD... make sure you put the spaces inside the quotes in or the string gets all jumbled together. — Scott Craner
– Scott Craner, Commented Jun 24, 2016 at 22:59
Spaces did it, geez i knew it was something simple. I even removed the single quotes b/c they werent necessary. Post as an answer and receive delicious feedbacks :) — Doug Coats
– Doug Coats, Commented Jun 24, 2016 at 23:02
Thanks--though for future reference it was the removal of the single quotes that fixed it, not the spacing--you can't compare number to string, while >'x'ORDER BY would have been ok (even without the space) if it was a string. — Paul X
– Paul X, Commented Jun 24, 2016 at 23:23

Parfait · Accepted Answer · 2016-06-24 23:13:18Z

4

While learning to build VBA queries, consider parameterized queries and avoid any need of quotes! This is an industry best practice across all languages when passing values in dynamic SQL queries.

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

   recordNum = 7

   With cmd
       .ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                 & "Data Source=C:\Users\Ashleysaurus\Desktop" & "\" & "xyzmanu3.accdb"
       .CommandType = adCmdText
       .CommandText = "SELECT * FROM Invoice" _
                        & " WHERE OrderNumber < ? ORDER BY OrderNumber ASC"    
   End With

   cmd.Parameters.Append cmd.CreateParameter("recordNumParam", adInteger, adParamInput, 10)
   cmd.Parameters(0).Value = recordNum

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

   rs.Close
   cmd.ActiveConnection.Close

   Debug.Print "Done!"

End Sub

answered Jun 24, 2016 at 23:13

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

3 Comments

Doug Coats Over a year ago

As much as I love this feedback, its a bit beyond my current level at the moment. I will save this link and learn to adjust, but currently i am still learning how to "make it work" ya know?

Doug Coats Over a year ago

if you want to link me some documentation to help me along that would be swell

Parfait Over a year ago

I hear you. Learn it as you go. Google is your friend on parameterized queries as there are many, many, many tutorials, blogs, posts, other SO answers on its use and importance

Paul X · Accepted Answer · 2016-06-24 23:02:36Z

2

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"

answered Jun 24, 2016 at 23:02

Paul X

3891 gold badge3 silver badges10 bronze badges

1 Comment

Doug Coats Over a year ago

you forgot a quote but you get feedbacks when itll let me check it as answered

Collectives™ on Stack Overflow

Pass VBA Variable into Access Query(Excel VBA)

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related