I am trying to load some data into R from an Access database. All of the instructions I can find say to use odbcConnectAccess("file.mdb") but I cannot seem to load this function. Has it been replaced or renamed? Is there another way to do this? I've loaded RODBC with library("RODBC") but still have no luck.
Thank you!
Here is what my session looks like. Does yours look similar?
library(RODBC)
setwd("C:/Users/Farrel/Documents/Working")
channel<-odbcConnectAccess("RRPre.mdb")
uniquehuman<-sqlFetch(channel, "AgeGenderofUniqueHumans")
It works perfectly on my system. Window 7, Microsoft Access around 2007. I do not know how proficient you are in Access databases. I used it for a few years and discovered that as long as I was the sole user it worked alright. The minute I tried to copy the database or share it or have a collegue add data to it it became unworkable. I have done all I can to work with it no more. If you have an ongoing use scenario then I guess you have to get odbcConnectAccess to work. However, if it is a one off then just export from Access to a more conventional data format, such as a csv table.
If you're using on a Mac, I've found success using Actual Technologies drivers to connect to Access databases. If you've already set up the ODBC connection, using ODBC Source Administrator on Windows or ODBC Administrator on a Mac, the rest is fairly straightforward.
library(RODBC)
# Open up channel to database supplying name of ODBC connection and user name
data.ch<-odbcConnect("ODBC_Connection_name",uid="user_name")
There is also an option to supply a password in odbcConnect, but I've set up the connection with the password included.
sqlTables(data.ch) # Query the connection for all tables
# Fetch all data from a table called "Table_name" or use standard SQL statements
df<-sqlFetch(data.ch,"Table_name")