1

I am using Python 2.7.8 32-bit to call MS Access 2007.

Below is the code I use: 

import sys
import pypyodbc
import os
import pyodbc

conn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=D:\Access\Geocoding.accdb;")
cnxn   = pyodbc.connect(conn)

I get the error message:

Traceback (most recent call last): File "D:\Access\get_Access.py", line 13, in conn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=D:\Access\Geocoding.accdb;") Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I searched online for a while and couldn't find anything wrong with the code.

Jen
  • 43
  • 1
  • 6
  • Possible duplicate of [Connecting to MS Access 2007 (.accdb) database using pyodbc](http://stackoverflow.com/questions/6396429/connecting-to-ms-access-2007-accdb-database-using-pyodbc) – midori Jan 04 '16 at 21:03
  • if I only ran the first line of code, it gives the same error message. – Jen Jan 04 '16 at 21:15
  • I tried the solution in Connecting to MS Access 2007 (.accdb) database using pyodbc , it doesn't solve the problem. – Jen Jan 04 '16 at 21:22

2 Answers2

0

I solved the problem. The Access database is created in Access 2013. I am try to use Python to connect it in Access 2007, that is the problem.

I created a Access 2007 database and copy all the data into it. Python can connect to it without problem.

Jen
  • 43
  • 1
  • 6
  • This is not the reason, *.accdb* files are Access 2007-2013 file types. There is no distinction between these years. – Parfait Jan 04 '16 at 22:09
  • yes, that should not be the reason. But, I can use the same code to connect Access database created in Access 2007 – Jen Jan 04 '16 at 22:11
  • So you have two MS Access installations on your machine: 2007 and 2013? I hope you are not confusing Access 2003 and earlier file types that use `.mdb` extensions. – Parfait Jan 04 '16 at 22:16
  • 1
    Also, do note, Python connects to the Jet/ACE SQL Engine (not the Access GUI program). It is a misnomer that Access is a database. It is actually a console that connects to the Windows Jet/ACE SQL (.dll files), not unlike PHPMyAdmin connects to MySQL or PgAdmin connects to Postgre, or Management Studio connects to SQL Server. – Parfait Jan 04 '16 at 22:16
  • the Access 2013 file is created from a different environment. The machine I am using python to call Access, only has Access 2007 installed – Jen Jan 04 '16 at 22:21
  • Still scratching my head on this one. Any file created in 2013 works in 2007 and vice versa. They are fundamentally the same. Your issue is a frequented one. In fact, I posted such a question a while back and usually it is due to the 32-bit/64-bit issue where the type of Python (32/64-bit) must align to the installed ODBC/OLEDB driver (32/64-bit). – Parfait Jan 04 '16 at 22:35
-2

Try to use double slash instead of slash in your db path. And write DBQ all uppercase

Your path should be something like this:

DBQ=D:\\Access\\Geocoding.accdb;
Bogdan Grigorovich
  • 1
  • 4
  • OP uses the [raw string](https://docs.python.org/2/reference/lexical_analysis.html#string-literals), `r`, literal which would escape single slashes. – Parfait Jan 04 '16 at 21:08
  • I changed to double slash, and tried both with and without 'r' , still not working. give same errors: File "D:\Access\get_Access.py", line 16, in conn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=D:\\Access\\Geocoding.accdb;") Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') – Jen Jan 04 '16 at 21:11