What is the correct syntax of RODBC query in R

Question

I'm trying to query an Access database using RODBC in R using the code below:

library(RODBC)
channel <- odbcConnectAccess("S:\\mypath\\PhysiPopDONOTTOUCH\\Physiology.mdb")

data <- sqlQuery( channel , paste ("select Endoscopy.*,
Histology.Diagnosis from Endoscopy JOIN PatientData ON
Endoscopy.HospNum_Id=PatientData.HospNum_Id  JOIN Histology ON
Histology.HospNum_Id=PatientData.HospNum_Id WHERE histology.VisitDate
= Endoscopy.VisitDate"))

. This is not successful as I get the error:

[1] "42000 -3506 [Microsoft][ODBC Microsoft Access Driver] Syntax
error in FROM clause."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select Endoscopy.*,
Histology.Diagnosis from Endoscopy JOIN PatientData ON
Endoscopy.HospNum_Id=PatientData.HospNum_Id  JOIN Histology ON
Histology.HospNum_Id=PatientData.HospNum_Id WHERE Histology.VisitDate
= Endoscopy.VisitDate'"

The tables Endoscopy and Histology are each one to many with the PatientData table. What am I doing wrong with the syntax?

I think you don't need the paste, and I find it helps adding 'as.is = TRUE', which prevents weird formatting conversions. So try: data <- sqlQuery( channel , "select Endoscopy.*, Histology.Diagnosis from Endoscopy JOIN PatientData ON Endoscopy.HospNum_Id=PatientData.HospNum_Id JOIN Histology ON Histology.HospNum_Id=PatientData.HospNum_Id WHERE histology.VisitDate = Endoscopy.VisitDate", as.is = TRUE) — Tom Evans, Nov 04 '16 at 10:16
I tried removing the paste and adding as.is=TRUE both independently and together but I get the same error. — Sebastian Zeki, Nov 04 '16 at 11:50
Have you tried running the SQL query in Access to check that it is valid? It looks like your SQL query is missing a semi-colon (;) at the end for instance. Maybe: data <- sqlQuery( channel , "select Endoscopy.*, Histology.Diagnosis from Endoscopy JOIN PatientData ON Endoscopy.HospNum_Id=PatientData.HospNum_Id JOIN Histology ON Histology.HospNum_Id=PatientData.HospNum_Id WHERE histology.VisitDate = Endoscopy.VisitDate;", as.is = TRUE) — Tom Evans, Nov 04 '16 at 13:06
Aha. Yes I re-designed the query with the Access visual designer and then cut and pasted the SQL into R. It works without the paste and without the as.is — Sebastian Zeki, Nov 04 '16 at 13:13

score 2 · Accepted Answer · edited May 23 '17 at 12:08

MS Access SQL requires JOIN pairings to be enclosed in parentheses. Also, use of paste() is to eliminate long whitespaces inside string values that appear when you break lines in R. And consider using table aliases to concisely reference tables. Include a semicolon terminator too (see: When should I use semicolons in SQL Server?). See below adjustment:

data <- sqlQuery(channel , 
                 paste("SELECT e.*, h.Diagnosis",
                       "(FROM PatientData p"
                       "INNER JOIN Endoscopy e p ON e.HospNum_Id = p.HospNum_Id)",
                       "INNER JOIN Histology h ON h.HospNum_Id = p.HospNum_Id",
                       "WHERE h.VisitDate = e.VisitDate;", sep=" "))

What is the correct syntax of RODBC query in R

1 Answers1