How to read from a 32 bit .mdb with 64 bit python and odbc driver

Question

Hello again community,

I'm looking for a solution to 32 bit .mdb's conflicting with my 64 bit environment: 64 bit Windows 7, with a 64 bit MS Access Database driver running 64 bit python. I want to write data from these .mdb's into a 64 bit PostgreSQL database and possibly also to .csv files. However, I would like this tool to not only work on my machine but also those of colleagues so it would ideally be able to handle other environments as well.

Initially I had a script writing to .csv with 32 bit versions of MS Access Database driver and python. However, I would like to create a QGIS plugin which I also had in 64 bit and I don't feel like changing everything to 32 bit just to be able to read the occasional 32 bit .mdb file.

I am completely new to coding, but I have read up on the issue and understand it's basically not possible to make a direct connection between a 32 bit database and 64 bit script and driver. Still, I find it difficult to believe that there is no solution to this issue. Do web apps not regularly deal with these kind of situations? Would it be possible to create this kind of functionality using Django for example instead? Or would using a costly driver claiming to support both 32- and 64 bit be a solution? Would it be easier to create a standalone tool? Or does such a tool already exist which I just haven't yet been able to find?

So to summarize: I'm looking to write data from a 32 bit .mdb to 64 bit PostgreSQL database preferably coded in a QGIS plugin.

The initial code I had working before is:

import csv, pyodbc
from tkinter import Tk
from tkinter.filedialog import askopenfilename

Tk().withdraw()
filename = askopenfilename()filenameEdit = filename.replace(":/", "://")

print(filename)
print(filenameEdit)

MDB = filename
DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
DBQ = filename
PWD = 'pw'

con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()

SQL = 'SELECT * FROM strips'  # This query would need to be expanded
rows = cur.execute(SQL).fetchall()
cur.close()
con.close()

mytable = input("Save file as: ")

def writeCSV():
    with open((mytable+'.csv'), 'w') as newDB:
        csv_writer = csv.writer(newDB, lineterminator='\n')
        for row in rows:
            csv_writer.writerow(row)

writeCSV()

# Another function should be added to write to a PostreSQL database

The error this gives now running 64 bit is:

con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))

pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2820 Thread 0x2758 DBC 0x65626f8
Jet'. (63) (SQLDriverConnect); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2820 Thread 0x2758 DBC 0x65626f8
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Cannot open a database created with a previous version of your application. (-1019); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2820 Thread 0x2758 DBC 0x65626f8
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2820 Thread 0x2758 DBC 0x65626f8
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Cannot open a database created with a previous version of your application. (-1019)")`

Any ideas will be very much appreciated, Many thanks!

Answer 1

Access database files (.mdb, .accdb) are not architecture-specific; there is no such thing as a "32-bit .mdb file" or a "64-bit .accdb file". There are 32-bit and 64-bit versions of the Access Database Engine (and drivers, etc.) but the database files they produce are identical.

You are receiving the

Cannot open a database created with a previous version of your application.

error because your version of the Access Database Engine no longer supports the version (not "bitness") of the database file you are trying to use. If you are using the Access Database Engine for Access_2013 or newer then your file format must be for Access_97 or older. If you are using the Access Database Engine from Access_2010 then the format of the file you are trying to use is super-old (likely Access_2.0).

As you seem to have noticed, we can still work with older database files using the legacy "Jet" database engine that ships with Windows (Microsoft Access Driver (*.mdb)) but it is a 32-bit driver and there is no 64-bit version.