Make Python access to MS Access faster on Windows

0
votes

I am on a windows server 2003 and accessing a locally stored MS Access 2000 MDB from python 2.5.4 scripts using pyodbc 2.1.5.

The db access is very slow this way (I am on fast machine and all other db operations are normal) and I wonder if there is a better way to access the MDB from python? Maybe a better odbc driver?

This is an example script like I use:

import pyodbc
cstring = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=t:\data.mdb'
conn = pyodbc.connect(cstring)
cursor = conn.cursor()
sql="UPDATE ..."
cursor.execute(sql)
conn.commit()
conn.close()
2
pythonwindowsms-accessodbcpyodbc

2 Answers

3
votes

Try setting up your connection once on program startup and then reusing the connection everywhere. Rather than closing it after every Execute or Commit.

0
votes

Tony's suggestion makes the most sense to me. However, if it's not enough, you could also try a later version of the driver, such as this one that works with Office 2007 files (as well as older versions, of course). You can download and install it even if you don't have Office.

Once you have it installed, try a connection string like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=T:\data.mdb;