"Too few parameters" trying to connect to a Microsoft Access database in R

Question

I'm using RODBC to connect to a microsoft access database. Some queries work fine, but on one I keep getting the errors:

07002 -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
[RODBC] ERROR: Could not SQLExecDirect 'SELECT baseunit FROM archiverapp_common_units WHERE unitname = "ng/ml"'

I am using the 'sqlQuery' function to make the query. The SELECT statement given in the error is what I intend it to be and works when I copy-paste directly into Access. From what I've gathered looking at similar posts this frequently results from misspelling a column or table name, but everything seems correct here.

Perkins

Might be a locale/encoding issue with your ng/ml have you tried typing in this query at the command line? – Riot

You got me fooling around with the command line and I figured out it apparently doesn't like you using double quotes, single ones work fine. – Perkins

Answer 1

Figured it out - apparently the answer was to use single quotes instead of double quotes in the WHERE clause. Changing the query string in my R code from this

'SELECT baseunit FROM archiverapp_common_units WHERE unitname = "ng/ml"'

to this

"SELECT baseunit FROM archiverapp_common_units WHERE unitname = 'ng/ml'"

solves the problem.

Perkins

Did you have to escape them? Posting your final command would be heplful – Hermaphrodite

Nope, no escaping if you use double quotes around the string in R. Although escaping doesn't hurt. – Perkins

Had the same issue. First I thought it was related to the UNION type of my query. After a few attempts narrowed it down to the used double quotes by calling field Format(DtTm, "Short Date") as Dt, . Replaced with single quotes; and, query call from RODBC::sqlQuery() in R works fine. Thanks! – Palmy