[R] Package RODBC sqlQueries

[Steven Ranney]

All -

I'm new to SQL and the RODBC package.  I've read the documentation
associated with the RODBC package, but I'm still having problems with
my SQL statements; I think my syntax, particularly with respect to my
WHERE statement, is off but I can't find any documentation as to why.

When I run a query from within the Access2007 database, it looks like this:

SELECT tblDataFieldRawSiteVisit.*
FROM tblDataFieldRawSiteVisit
WHERE (((tblDataFieldRawSiteVisit.dataForm)="Oyster Transition Plan
Site Mapping Detail"));

After inserting that (verbatim) into my R code like this:

testData = sqlQuery(db, SELECT tblDataFieldRawSiteVisit.*
FROM tblDataFieldRawSiteVisit
WHERE (((tblDataFieldRawSiteVisit.dataForm)="Oyster Transition Plan
Site Mapping Detail"));)

I get the following error message:

Error in source(.trPaths[5], echo = TRUE, max.deparse.length = 150) :
  C:\Users\sranney\AppData\Roaming\Tinn-R\tmp\selection.r:2:32:
unexpected symbol
1: db <<- odbcConnectAccess2007(paste(dbPath, dbName, sep = ''))
2: testData = sqlQuery(db, SELECT tblDataFieldRawSiteVisit.
                                 ^

Previous RODBC/SQL statements used by other coders at my firm have a
single quotation mark around the SQL query, so I try that:

testData = sqlQuery(db, 'SELECT tblDataFieldRawSiteVisit.*
FROM tblDataFieldRawSiteVisit
WHERE (((tblDataFieldRawSiteVisit.dataForm)="Oyster Transition Plan
Site Mapping Detail"));')

I get no error messages, but I when call for testData, I get the following:

> testData
[1] "07002 -3010 [Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT
tblDataFieldRawSiteVisit.*\nFROM tblDataFieldRawSiteVisit\nWHERE
(((tblDataFieldRawSiteVisit.dataForm)=\"Oyster Transition Plan Site
Mapping Detail\"));'"

I can run SELECT FROM statements successfully.  I cannot run SELECT
FROM WHERE statements without running into some sort of error.

Can anyone offer advice as to why I'm having an issue with the WHERE
part of this SQL query?  I have double checked spelling on my
databases, columns, and values.

Thank you -

SR
Steven H. Ranney