string variables concatenated in SQL (to access DB), Syntax errors due to quotations?

Question

I have 2 list boxes defining 2 variables that need to be passed to OpenRecordSet via a SQL statement.

Conveyor_ID is a access table name
Width is a field name in the table Conveyor_ID
Width_Row is a string value that values in the Field Width

Dim db As DAO.Database
Dim rs As DAO.Recordset
Const DbLoc As String = "DB location info"


Dim Conveyor_ID As String  ' Conveyor ID Table
Dim Width_Row As String    ' Width row, string values such as 12" or 12in

Set rs = db.OpenRecordset("SELECT * FROM " & [Conveyor_ID] & " Where Width = " & [Width_Row])

Depending on which value is selected for the Width_row, I will get two different errors.

If ' 14in ' is selected, error of " In operator without () in query expression 'Width = 14in' "

If ' 14" ' is selected, error of " Syntax error in string in query expression 'Width = 12"'.

I believe I don't understand the syntax of adding variables to a SQL string that is getting passed onto a Openrecordset method.

Erik A · Accepted Answer · 2018-01-09 20:01:14Z

3

The problem is you're passing the string without any quotation marks, and concatenating it with the SQL. This results in invalid SQL.

This means your final string might look something like:

SELECT * FROM someTable Where Width = 12in

That's not valid SQL. Strings in SQL need to be surrounded in single or double quotes.

There are multiple ways to go about this. The proper way is to use parameters.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Const DbLoc As String = "DB location info"


Dim Conveyor_ID As String  ' Conveyor ID Table
Dim Width_Row As String    ' Width row, string values such as 12" or 12in
Set db = CurrentDb 'You were missing this in your sample
Set qd = db.CreateQueryDef("", "SELECT * FROM " & [Conveyor_ID] & " Where Width = @theWidth")
qd.Parameters("@theWidth") = [Width_Row]
Set rs = qd.OpenRecordset

edited Jan 9, 2018 at 20:01

answered Jan 9, 2018 at 19:16

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

5 Comments

Mathieu Guindon Over a year ago

Amen! Parameters FTW! OP's way is a recipe for SQL injection attacks, or more casually, for poor Dougal O'Connor to never be able to register his full name correctly!

Eric Over a year ago

qd.Parameters(@TheWidth) = [Width_Row] Gives an error due to @ being an "invalid character", when removed, there is an error "Item not found in the collection" Is there an alternative way to identity the variables in the SQL statement for the parameter input?

Erik A Over a year ago

Sorry, missed some quotes there myself. Current edit should work

Eric Over a year ago

Edit AH thanks I got it! " " work instead of @. everything works now (still brand new @ vba/access, thanks for the fundamentals

Erik A Over a year ago

Those @'s are not strictly required. You can just enter any string in place of @width. It's a convention I use, which is common to people using T-SQL

Collectives™ on Stack Overflow

string variables concatenated in SQL (to access DB), Syntax errors due to quotations?

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related