VBA 3131 error in SQL using variables in query

Question

Can you please help me? I was trying to figure it out for about 2 hours but I still have some error in the syntax.

I have the following code with String variables which I need to pass into the SQL query in VBA script, but I am keep getting some syntax errors.

DoCmd.RunSQL "Delete * From " '" & [tableName] & "' & " Where (" '" & [tableFieldName] & "' & " = " & '" & [tableRecord] & "')"

Thank you very much for some advice.

First apostrophe is outside of text and thereby treated as comment. Of course if it would be inside of the string, then it would give syntax error also - table and field names should not be written inside apostrophes. And, if it is real SQL, then delete syntax doesn't include asterisk (*) - what kind of DBMS you are using? — Arvo
– Arvo, Commented Sep 10, 2020 at 10:19

jamheadart · Accepted Answer · 2020-09-10 10:34:07Z

1

I think you were going for this:

DoCmd.RunSQL "Delete From [" & TableName & "] Where [" & tableFieldName & "] = '" & tableRecord & "'"

Where I assume TableName, tableFieldName and tableRecord are variables??

edited Sep 10, 2020 at 10:34

answered Sep 10, 2020 at 10:24

jamheadart

5,3737 gold badges37 silver badges87 bronze badges

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

3 Comments

M_V Over a year ago

Thank you very much for your help. TableName, tableFieldName and tableRecord are indeed variables, however I'm still getting the syntax error. Now it is the 3450 error.

jamheadart Over a year ago

Actually I changed my answer it wasn't quite right, I have no surrounded the TableName and the tableFieldName with square brackets []

jamheadart Over a year ago

Also are you using Access here and not Excel?

Collectives™ on Stack Overflow

VBA 3131 error in SQL using variables in query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related