-1

I am trying to connect and MS Access database to R and create a dataframe and eventually plot some data, but I get this error, while it seems I am importing the data from a table correctly. Where is the problem?

Console:

   > # Load RODBC package
> library(RODBC)
>
> # Connect to Access db
> db_address <- "S:/LKF/Interdepartmental/DataGovernance/Tools/MM_Backend.accdb"
> db <- odbcConnectAccess(db_address)
Error in odbcConnectAccess(db_address) :
  odbcConnectAccess is only usable with 32-bit Windows
>
>
> # Get data
> sql <- "select * from tblMaterials"
> df1 <- sqlQuery( db , sql)
Error in sqlQuery(db, sql) : first argument is not an open RODBC channel

Code:

# Load RODBC package
library (RODBC)

# Connect to Access db
db_address <- "S:/LKF/Interdepartmental/DataGovernance/Tools/MM_Backend.accdb"
db <- odbcConnectAccess(db_address)


# Get data
sql <- "select * from tblMaterials"
df1 <- sqlQuery( db , sql)
Ibo
  • 4,081
  • 6
  • 45
  • 65
  • 1
    Are you sure you're not getting an error before this? `Str (df1)` should throw an error because it should be `str(df1)` and what does that return? – MrFlick Mar 27 '18 at 19:29
  • No, the entire code is acting weird. I feel there is a bug in R. I can remove str(df1) and still I get the same error – Ibo Mar 27 '18 at 19:30
  • when I re-run the code, sometime I get this error `Error in sqlQuery(db, paste(sql)) : could not find function "sqlQuery"` – Ibo Mar 27 '18 at 19:31
  • 1
    Well, "there is a bug in R" is a pretty broad claim. R isn't really the type of language where "sometimes" you get an error. Are you trying on different machines or something? When asking for help, you should include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. – MrFlick Mar 27 '18 at 19:32
  • @MrFlick I have been around enough to understand what I say. I think my question is clear enough and I am not claiming anything. I am just explaining my experience when I rerun the code. Why would I get different error messages and sometimes no error messages? and seriously, how can I give you reproducible example while the problem is a pretty simple and straightforward connection problem to a database on a private server? – Ibo Mar 27 '18 at 19:39
  • 2
    MrFlick is genuinely trying to help and merely expressing the (reasonable) position that this is very unlikely to be the result of a bug in R. Not finding the function `sqlQuery` would tend to be because you haven't loaded the RODBC package. `hist()` throwing an error saying it can't find `df1` would only happen if `df1` really didn't exist, which means `str(df1)` should also give an error. Can you reproduce any of this behavior without the database? – joran Mar 27 '18 at 19:49
  • Ok, I found out some problems, 1st the address must be forward slash and not the backslash, 2nd the address did not have a database name at the end of it. Right now with the edited code I am getting a clear connection failed error – Ibo Mar 27 '18 at 19:56
  • Glad you made some progress, and that behavior does make more sense. Unfortunately, I'm not going to be any help debugging MS Access ODBC connection issues; no experience there. – joran Mar 27 '18 at 19:58

1 Answers1

0

I figured it out using this post: How to connect R with Access database in 64-bit Window?

I have a 64-bit windows and my office products are all 32-bit so I had to make R-32 bit as the default and change the connection definition:

odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=S:/LKF/Interdepartmental/DataGovernance/Tools/MM_Backend.accdb")
Ibo
  • 4,081
  • 6
  • 45
  • 65