0

In a Python script with pyodbc, I am trying to connect to a still used Access 97 database on our network but I have problems to get the connection (or the connection string) to work.

I keep getting the following error:

   pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnectW)')

This is my connection string which fails with above mentioned error:

conn = pyodbc.connect('Driver={Microsoft Access Driver (.mdb)};Provider=Microsoft.Jet.OLEDB.4.0;Password=mypassword;User ID=myusername;dbq=\\fileserver\\conta\\locationdir\\mydatabase_be.mdb;Persist Security Info=True;Jet OLEDB:System database=\\FILESERVER\\backend\\mdw\\system.mdw')

As you can see, this connection requires to use a System database (workgroup - mdw). The database is on a network-storage.

I am using Python 2.7.

Can anyone help?

EDIT: in the connection-string, the backslashes should be double backslashes. It seems like only a single backslash is shown.

Shmil The Cat
  • 4,548
  • 2
  • 28
  • 37
moster67
  • 830
  • 3
  • 12
  • 30

2 Answers2

1

I found that you had to use python 32 bit - not because the database is 32 bit but because Access97 is so old you have to use the old 32 bit odbc drivers not the new 64 bit drivers that python 3.9 64 bit uses in windows 10

Andrew Johns
  • 11
  • 1
  • 1
    Yes, 32-bit Python and the `Microsoft Access Driver (.mdb)` ("Jet") 32-bit driver. However, that is still not bullet-proof, e.g., [here](https://github.com/gordthompson/sqlalchemy-access/wiki/%5Bfaq%5D-support-for-Access_97-and-earlier). – Gord Thompson Aug 06 '21 at 00:26
0

OK, I got it sorted. My solution was based on the following snippet (was just a matter of getting the syntax right):

strConnection = (r"Driver={Microsoft Access Driver (*.mdb)};"
    r"Dbq=C:\\VC Projects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
    r"SystemDB=C:\\Program Files\\Microsoft Office\\Office\\SYSTEM.mdw;"
    r"Uid=Carlos Antollini;Pwd=carlos")
conn = pyodbc.connect(strConnection)
#your code

Solution found in this web-page

Hope this may be useful for someone else.

moster67
  • 830
  • 3
  • 12
  • 30