We help IT Professionals succeed at work.

How to connect MSACCESS 2019 using pyodbc?

I must connect to an old Microsoft Access DB (.mdb) using python pyodbc.

Here's what i've installed on my computer:
  • Microsoft Access 2019, 64-bit
  • Microsoft Visual Studio Code
  • Python 3.7.4, 64-bit
  • I've done the pip install pyodbc

I've tried several connection strings, but none of them work.  I always get an error message
('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
.


Here's some code i've tried:
_ConnStr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\TEMP\\MYFILE.MDB;Persist Security Info=False;'
_conn = pyodbc.connect(_ConnStr)

Open in new window


I've seen previous post about people using 32 bits of python vs access 64 bits, but it's not my case.  I'm using 64 bits for python and for access.

Thanks you
Comment
Watch Question

BRONZE EXPERT

Commented:
If the old Access db was 32 bit, then you'll need 32 bit drivers. You don't say which version of Access the old db was developed in. It is possible that the 2019 version will not be able to "Talk" to that earlier version. A 64 bit copy of Access will not be able to use a 32 bit Access database. - 32 and 64 bit Access are really 2 separate products.

Kelvin
BRONZE EXPERT

Commented:
if you are working in a windows box check under control panel/administrative tools/ODBC data Sources for both 32 and 64 bit and see if you have something missing there
Jim Dettman (EE MVE)President / Owner
GOLD EXPERT
Fellow
Most Valuable Expert 2017

Commented:

Try connecting to the DB using the ODBC applet.


For 64 bit, use the one in   C:\WINDOWS\System32\odbcad32.exe


If you can create a DSN and use the test button there OK, then you can just point to the DSN in Access.   Long term, you'd be able to figure out the connection string from that.


And when you create the DSN, but a _64 on the end of it so you can tell you are using the right one.


Jim.

John TsioumprisSoftware & Systems Engineer
BRONZE EXPERT
Distinguished Expert 2019

Commented:
Have you checked the documentation of pyodbc : https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-Microsoft-Access
BRONZE EXPERT

Commented:
check what drivers you have in yor system

Python 3.8.1 (tags/v3.8.1:1b293b6, Dec 18 2019, 22:39:24) [MSC v.1916 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]
['Microsoft Access Driver (*.mdb)', 'Microsoft Access Driver (*.mdb, *.accdb)']
>>>


there are some examples on the pyodbc wiki which may help you
John TsioumprisSoftware & Systems Engineer
BRONZE EXPERT
Distinguished Expert 2019

Commented:
It seems that the connection string should be
_ConnStr = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};Data Source=D:\TEMP\MYFILE.MDB;')

Open in new window

Author

Commented:

arana: the line of code you have provided with pyodbc.drivers() doesn't list Microsoft Access Driver.   


about dsn: i've both version installed (32/64), but Microsoft Access Driver is only listed in 32 bit version.  And according to the article mentionned by John, it is not recommended to try to install another version of the driver other than the one installed by Office itself.   I don't understand why it installed a 32 bits version when my Office installed is definitely 64.  


john: the connection string you provided give me the same error.  As Kevin mentionned, it's probably due to a mismatch of versions.  Python 64 probably can't access a 32 bit driver.  I'm actually try a repair to see if i'll have any option to install the 64 bit version of the ODBC driver.


In the meantime... i've access to an older computer with MS Access 2013, 32-bit, python 3.8 32-bit.  So I'll give it a try. 

John TsioumprisSoftware & Systems Engineer
BRONZE EXPERT
Distinguished Expert 2019

Commented:
just use the code from the wiki to ensure what driver is available (the same posted by arana)
if you get that you have the correct driver (.accdb) then probably you have a problem with the .mdb
BRONZE EXPERT
Commented:
If you see an empty list then you are running 64-bit Python and you need to install the 64-bit version of the "ACE" driver.

since you already have the 32 bit version then you will need to either use 32 bit python, or uninstall de 32 bit ACE driver and install the 64 bit one

Author

Commented:

Repair Office:  Didn't worked.  It never give me any choice, and at the end, the same drivers were installed but not this one.


Tests on older computer:

It has both drivers and it worked with my original code.

  • Microsoft Access Driver (*.mdb, *.accdb)
  •   Microsoft Access Driver (*.mdb)


I might give a try to install the 64-bit driver and see if it solve the problem, or cause an havoc to my Office install.


Author

Commented:

Before breaking anything in my Office installation, i've done a last test:  I've installed python 3.8.x 32-bit on my new machine and it's working.


Thanks a lot for the help you've provided