5

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.

JaredL
  • 1,372
  • 2
  • 11
  • 27
  • Might be a locale/encoding issue with your `ng/ml` have you tried typing in this query at the command line? – Brandon Bertelsen Jul 25 '14 at 21:01
  • 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. – JaredL Jul 25 '14 at 21:21

1 Answers1

8

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.

JaredL
  • 1,372
  • 2
  • 11
  • 27
  • Did you have to escape them? Posting your final command would be heplful – Brad Jul 25 '14 at 21:24
  • Nope, no escaping if you use double quotes around the string in R. Although escaping doesn't hurt. – JaredL Jul 25 '14 at 21:46
  • 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! – Oleg Melnikov Aug 14 '16 at 01:07