Issues accessing MS Access database using python - Data source name not found and no default driver specified

Dear all,

I'm having troubles using python to run queries on an MS Access 2013 DB.  At the moment I am trying to get the example from the link below to work.  

https://code.google.com/p/pyodbc/wiki/GettingStarted

I have a problem with this line in code

connection = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\\Test\database.accdb;')

The error is
connection = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Test\database.accdb;')
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I tried to create a User DSN when I select Add the Microsoft Access option isn't there.

In the User DSN tab Under User Data Sources "MS Access Database - Microsoft Access Driver (*.mdb, *.accdb) exists.  When I select configure the error message appears "The setup routines for the Miscrosoft Access Driver (*.mdb, *.accdb) ODBC driver couldnot be found.  Please reinstall the driver.

Another pop up appears "the specified DSN contains an architecture mismatch between the Driver and Application.

Then I looked into reinstalling the driver no change.
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

I hope you can help.

Thanks
AndyC1000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
Did you install the Python ODBC driver for MSAcess first, before trying to create your DSN?
https://code.google.com/p/pyodbc/downloads/list
AndyC1000Author Commented:
I downloaded and pip installed pyodbc-3.0.10-cp27-none-win_amd64.whl from http://www.lfd.uci.edu/~gohlke/pythonlibs/


The code has no error with the import pyodbc statement.

I'm not sure if I should follow the suggestion from this link and install the 32 bit version.  Although the post is from 2011.
http://stackoverflow.com/questions/4548929/cannot-connect-to-access-db-using-pyodbc
Robert ShermanOwnerCommented:
Just taking a shot in the dark here, but try changing the source to

connection = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\\Test\\database.accdb;')

(note the second backslash just before the filename)

Since you're escaping each backslash character so that it is actually passed through to the driver as a backslash, you'll need to make sure ALL of the backslashes are escaped by doubling them.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LajuanTaylorCommented:
pyodbc-3.0.10-cp27-none-win_amd64
-  Is your computer an AMD based system or an Intel? It looks like you have to select the pyodbc install based on your computer processor type as well.

Also, there might be some differences depending on which site you obtain the install from as well:
AndyC1000Author Commented:
I tried the second backslash just before the filename it didn't change the error.

I chose the AMD version of the library because it was the only 64 bit version.

I will look into those links
AndyC1000Author Commented:
I installed the pyodbc-3.0.7.win-amd64-py2.7.exe library from https://code.google.com/p/pyodbc/downloads/list

How do I know if its been installed successfully ?  I checked the in the Drivers tab of the ODBC Data Source Administrator only SQL Server drivers are in the list.

I also tried saving the test database to mdb format.  No change.
Robert ShermanOwnerCommented:
This: "the specified DSN contains an architecture mismatch between the Driver and Application." leads me to believe that you are right about your earlier hunch on needing the 32-bit version.  

What version of MS-Office/Access are you using?   You can quickly find this out by launching Access and going to "Help" under the File Tab.   If you're using 32-bit, you likely need to install the 32-bit drivers as well.

EDIT:  Also, sort of an aside at this point, the 64-bit drivers are all going to be named 'something-amd64...' this is just the architechture and you would use the same drivers for 64-bit whether your CPU was manufactured by Intel or AMD.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndyC1000Author Commented:
I found the issue!  The shortcut for setting ODBC data sources was pointing to the 32bit data sources instead of 64bit.

Go to control panel -> administrative tools --> select data sources(ODBC) --> then right click on that file --> go to properties --> in the shortcut tab -> change the path from %windir%\System32\odbcad32.exe to

%windir%\SysWOW64\odbcad32.exe
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.