Passing a variable into SQL With VBA/Access

Question

I have a Case Statement that reassigns a string variable [Conveyor_ID] depending on the case. Then I pass this variable via a SQL statement into a openrecordset method.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Const DbLoc As String = "database location"
Set db = OpenDatabase(DbLoc)
Set rs = db.OpenRecordset("SELECT Width FROM $[Conveyor_ID]%")

I am getting a error stating " invalid bracketing of name "$Conveyor_ID]%" "

I have tried a few variations, but I don't quite seem to know how to pass a variable into a SQL statement. Can anyone point me in the right direction?

Thanks

(Brand new, Mech Eng. trying to make some useful programs!)

duffmanseven · Accepted Answer · 2018-01-05 00:09:27Z

2

Can we see how this Conveyor_ID is being defined? It might make it easier to answer the question.

Assuming your [Conveyor_ID] is defined as Conveyor_ID in the VBA function, why not try

Set rs = db.OpenRecordSet("SELECT Width FROM " & Conveyor_ID)

answered Jan 5, 2018 at 0:09

duffmanseven

1752 silver badges10 bronze badges

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

Comments

Jennifer · Accepted Answer · 2018-01-06 17:57:19Z

0

MS Access VBA concatenates strings with &.

In your recordset, the SQL string needs to be changed to:

Set rs = db.OpenRecordset("SELECT Width FROM " & Conveyor_ID & ";")

Edit: You also might need the semicolon at the end of the SQL. Added semicolon.

Edit2: Since you clarified that Conveyor_ID is a string variable, then you will need to add single quotes around it. My previous answer works for numbers. Dates would have # in place of single quotes.

And as Erik von Asmuth mentioned below, it looks like semicolons aren't required in Access in the VBA window. It's a personal preference.

Your code for the recordset should now look like this with the string:

without semicolon:

Set rs = db.OpenRecordset("SELECT Width FROM '" & Conveyor_ID & "'")

with semicolon:

Set rs = db.OpenRecordset("SELECT Width FROM '" & Conveyor_ID & "';")

I just noticed:

1. The brackets - you can't use them in VBA on a variable or argument. You would use them in SQL, for example, if your table name or field name had a space in it (see below code for example table name with space), but it's bad practice

2. You are missing your table name and WHERE clause in your SQL string (if this is your full SQL string and not pseudocode)

Set rs = db.OpenRecordset("SELECT Width FROM [table Name] WHERE fieldName = '" & Conveyor_ID & "'")

edited Jan 6, 2018 at 17:57

answered Jan 5, 2018 at 0:08

Jennifer

274 bronze badges

1 Comment

Erik A Over a year ago

Semicolons aren't required in Access sql

Collectives™ on Stack Overflow

Passing a variable into SQL With VBA/Access

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related