Connecting to MS Access DB using ODBC package

New to Julia
1

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
2

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
3

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?

4

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
5

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.

6

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

7

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
8

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

1 Like