Add database page - MS Access about db.rstudio.com HOT 2 OPEN

Hi @DavisVaughan, would you happen to have a sample connection code I can use for a MS Access page?

from db.rstudio.com.

Sure. I just ran this so I know it works. A few prereqs.

• This is for a 64 bit Access Database.
• There are a number of versions of Access. I would send people to this site for connection string help.

With no security, connect with any of:

# Can use both \\ and /
dbConnect(odbc::odbc(), 
          Driver="{Microsoft Access Driver (*.mdb, *.accdb)}",
          Dbq="C:\\Users\\Administrator\\Desktop\\Access\\Database1.accdb")

dbConnect(odbc::odbc(), 
          Driver="{Microsoft Access Driver (*.mdb, *.accdb)}",
          Dbq="C:/Users/Administrator/Desktop/Access/Database1.accdb")

# Brackets not needed
dbConnect(odbc::odbc(), 
          Driver="Microsoft Access Driver (*.mdb, *.accdb)",
          Dbq="C:/Users/Administrator/Desktop/Access/Database1.accdb")

With database password security, connect with:

dbConnect(odbc::odbc(), 
          Driver="{Microsoft Access Driver (*.mdb, *.accdb)}",
          Dbq="C:\\Users\\Administrator\\Desktop\\Access\\Database-with-pw.accdb",
          Pwd="password")

Supposedly you can have users, but I haven't used it (and can't even figure out how to add users).

dbConnect(odbc::odbc(), 
          Driver="{Microsoft Access Driver (*.mdb, *.accdb)}",
          Dbq="C:\\Users\\Administrator\\Desktop\\Access\\Database-with-pw.accdb",
          Uid="username",
          Pwd="password")

I know you guys encourage using DSN's. You can do that too. After setting up a DSN in the ODBC Data Source Administrator 64bit, the below code connects. Note that you DON'T need Pwd if you set up your ODBC DSN to use a password, which is set in the "Advanced" settings, but do otherwise.

dbConnect(odbc::odbc(), dsn = "access-tester", Pwd="password")

A few other things:

I just set this up on a Windows AWS server to answer your question. There are a few things people definitely need to know.

If using Access 32 bit:

• You need to use ODBC Data Source Administrator (32 bit)
• You will likely already have the correct driver for connecting. I think it came with Access, or just Windows generally.
• The Driver argument is likely Microsoft Access Driver (*.mdb), slightly different than 64 bit.
• You will also likely need to change to the 32 bit version of R to connect. From RStudio, Tools->Global Options->General then select Change... to switch.

If using Access 64 bit:

• You need to use ODBC Data Source Administrator (64 bit)
• You probably don't have the correct driver. If in ODBC Data Source Administrator (64 bit) you don't see Microsoft Access Driver (*.mdb, *.accdb) then you don't have it. Send people here and have them download AccessDatabaseEngine_X64.exe (not AccessDatabaseEngine.exe). Its a simple download, and once it is finished the driver should be there in the Data Source Administrator.

What architecture of Access do I have?

• Open Access -> Before opening a DB, click "Account" on the left bar of options -> Click "About Access" -> It should say 64/32 bit at the top.

from db.rstudio.com.