I can’t figure out why my SQL statement doesn’t work

I’m new to SQL and thought I’d write a simple statement to see how it works. I first connect to a database which contains a table called LuPull. All I’m trying to do is select all the rows from LuPull where the Service Number is A107118, but it can’t find the table.

I’ve updated the packages, tried all combinations of the SQL statement, used

sqlColumns(pers, "LuPull")$COLUMN_NAME

which did return all the column names, so I know it sees the table, and I even used Access to generate the query and copied and pasted the Access SQL code, but all I get is this error:

Error in result_create(conn@ptr, statement) : no such table: LuPull

library(RODBC)
library(sqldf)
db <- "C:/Projects/MFData2005-2015.accdb"
pers <- odbcConnectAccess2007(db) 
A107 <- sqldf("SELECT * FROM LuPull WHERE LuPull.[Service Number]='A107118'")
View(A107)
close(pers)

Answer

You don’t need sqldf for working with Access database. Use sqlQueryfrom RODBC library to select data instead.

library(RODBC)
db <- "C:/Projects/MFData2005-2015.accdb"
pers <- odbcConnectAccess2007(db) 
A107 <- sqlQuery(pers,"SELECT * FROM LuPull WHERE LuPull.[Service Number]='A107118'")
View(A107)
close(pers)

RODBC Docs

Advertisement

Answer