0

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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Angus
  • 355
  • 2
  • 12
  • I am not an 'R' developer but the documentation for [sqldf](https://cran.r-project.org/web/packages/sqldf/README.html) says it works with SQLite, H2, PostgreSQL or MySQL. On line #3 you are referencing an MS Access 20007 database. Are you sure sqldf works with MS Access? Perhaps [this question on StackOverflow](https://stackoverflow.com/questions/13070706/how-to-connect-r-with-access-database-in-64-bit-window) can help you. – ShellDragon Jan 26 '19 at 21:44
  • I removed the sqldf and just ran it as a standalone sql statement, which also didn't work. – Angus Jan 26 '19 at 21:57

2 Answers2

1

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

ShellDragon
  • 1,712
  • 2
  • 12
  • 24
0

MS Access does not use single quotes to delimit strings; it uses the non-standard double quotes.

Perhaps phrasing the query as valid MS Access syntax would help:

SELECT *
FROM LuPull
WHERE LuPull.[Service Number] = "A107118"
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon, I tried that and even added a sqlFetch statement, but I get an "unknown token LuPull" error message. – Angus Jan 26 '19 at 21:56
  • @AngustheCat . . . `LuPull` is a table name. That makes it unlikely that is a column name -- not impossible, but unlikely. – Gordon Linoff Jan 26 '19 at 21:57
  • Unless the data volume is huge, you may diagnose it with a simple `select * from LuPull`, without using filters – ShellDragon Jan 26 '19 at 22:06