Excel VBA, Syntax error (missing operator) in query expression

Question

I'm attempting to retreive a resultset from a MS Access database using VBA for Excel. In the VBA code, I'm constructed a string equal to:

strSql = "SELECT * FROM Pricing WHERE Account In (''1234'', ''ABCD'') '; "

Note that there are 2 single quotes around the strings within the SQL statement. There is also a single quote before the semi-colon. If I'm not mistaken, this evaluates to:

SELECT * FROM Pricing WHERE Account In ('1234', 'ABCD') ;

This query works fine when run directly in MS Access. However, in Excel VBA, I keep getting the Run-time error:

Syntax error (missing operator) in query expression 'Account In (''1234'', ''ABCD'') '; '

Notice that this error actually cut off the first half of the SQL statement.

I've tried a few variations, using double-quotes, double double-quotes, no quotes, etc. etc.

Any advice?

Thanks.

Answer 1

In Excel VBA the string identifier is the " (double quote) character. You do not need to double the single quote characters for them to pass through to the database when enclosed by double quotes.

Try this:

strSql = "SELECT * FROM Pricing WHERE Account In ('1234', 'ABCD')"