Connecting to MS Access DB using ODBC package

Hi folks,

I have been using Julia casually for a few months now, I am not a trained programmer in any form.
I am completely stumped on getting the ODBC package working to connect to a single access DB(.accdb) file.

I am trying to replicate something I have working in python where I built the connection strings and then passed them to PyODBC.

I see ODBC.connect() being used from forum posts online, but it doesn’t seem to be a method anymore?

I have read through both versions of the documentation and still cannot get the syntax correct.
What I have so far:

using ODBC

cnxn_str = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq="
db = "C:/Path/To/Folder/Scrap.accdb"

#conc. strings
dsin = "$cnxn_str$db"

conn=ODBC.DSN(dsin)

Can I use the ODBC package in this manner or do I need to actually make the file DSN in windows?
What is the correct syntax to connect to a standalone MS access database file?

The access and excel drivers shows up when I run listdsns() so that seems to be working.
I am using Julia 0.5.0 on windows 10, and have the latest version of ODBC.jl installed (0.5.1)

Thank you for your help.

1 Like

I ended up figuring it out (even though it took a while…).

I uninstalled then re-installed the x64 version of MS access engine Link to MS help site with download

Following that, I can write it as:

cnxn_str=ODBC.DSN("Driver={Microsoft Access Driver (*.mdb, *.accdb)};
 DBQ=C:Path/To/Folder/Scrap.accdb")  #build dsn first

ODBC.query(cnxn_str,"SELECT * FROM Table1") #query to pull table in as dataframe

and now everything seems to be functioning properly.

2 Likes

Sorry to revive this old thread, but I also struggle to get data from Access to Julia.
The method described above does not work anymore because ODBC.DSN does not exist anymore in the current version of ODBC.jl.

I tried to directly create a Connection object using the Connection String, e.g.

con = ODBC.Connection(raw"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=c:/temp/Input_data.accdb")

but this (and variations of it I tried) does not work.
The driver itself is visible in the “ODBC Data Source Administrator” in Windows.
I am using Access 2016 32-bit in Win 10.

Has anyone recently got this working and could help me?

This isn’t exactly what you asked, but I’ve been using JDBC to read from Access. While it does add a java dependency, it has the advantage of avoiding the whole 32-bit/64-bit architecture issue.

This (unregistered) package can be used to test, or for inspiration on how to set up with the UCanAccess driver: GitHub - hellemo/Access.jl: Julia support for MS Access database files via JDBC and UCanAccess

1 Like

Looks great, thanks!
I am not restricted to use ODBC, any way to get data from Access diretctly to Julia is fine for me.
I encounter an error when building and created an Issue for it, it would be great if you could take a look.

I have not tried but I am aware of some of the likely setup issues: Do you you have 64-bit Win? Can you connect to that Access 32-bit app from something else than Julia?

32-bit ODBC is difficult to configure for 64-bit Win. My ODBC solution required separate symbolic links for 32-bit and 64-bit ODBC, as described in this article (you can ignore the Oracle specifics):

http://realfiction.net/2009/11/26/use-32-and-64bit-oracle-client-in-parallel-on-windows-7-64-bit-for-e-g-net-apps

Sorry about that, looks like I made some mistakes when transitioning to using Artifacts. I’ll have a closer look later, until then you might want to give an earlier version a try (0.2.1 I think), where the jar-files are bundled directly.

1 Like

Thank you, it is working for me with v0.2.1.

1 Like