I have already write here about how I am enjoying with Python, although I still have a lot of things to learn. Now I am starting to program scripts to work with databases and … I have to face my first serious problem: dealing with Microsoft Access databases with Python.
On this blog I have write about installing new Python modules in several ways, and with examples. One of them was installing mysql-db to work with MySQL databases. Other useful package is sqlite3 to work with SQLite databases, and so on. Another possibility I talk about is to instalar new modules from source code. The main characteristic of this modules is to make easy working with databases. But for working with MS Access, there is no package-module.
What happend with Microsoft Access?
The answer is easy: nthere are no package or module to deal directly with Microsoft Access databases, and the solution to do it is different depending if the operating system is Windows XP or Windows 7. And I think this is a serious problem!
In theory, the problem would be solved just by modifying the connection string, but we can experiment that with the same souce code, the outcome is … totally different depending if this code is running on Windows XP or on Windows 7.
Connection string. The source of the problem
In computing, a connection string is a string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider in order to initiate the connection. Whilst commonly used for a database connection, the data source could also be a spreadsheet or text file.
When I started to program, with Visual Basic 6, one of the things I tried to understand was about creating connection string, and I used this website: www.connectionstrings.com. In order to make easy dealing with databases, I end up writting my own library that I have been improving during several years (and I still do), depending on my needs. I save a lot of time, and I will talk about this librery in the future!.
There are connection string in Python
Now that I am starting to feel confortable with Python, I found that connection string are also neccesary if you want to work with certaing type of files (MS Access, MS Excel, …). And even more, because you need to choose between several engines: OLE Db, ACE.
And it can be even worse, if you mix a Windows XP with a 64 bits operating system. All due to Microsoft.
Windows 7 and the Jet OleDb 4.0 engine problems
The Jet OleDb 4.0 engine is the main engine if you want to work with Microsof Access databases, and that’s the one I use to work with. But with 64 bits operating systems, the solution is not so simple because …
There are no Jet Ole Db engine on 64 bits machines.
Here you can find a blog that deals with this problem: http://govindkanshi.wordpress.com/2008/11/29/accessing-jet-db-on-64-bit-windows/. If you have any doubt, you can read even a Microsoft blogs, and you can read it as well:
Native 64-bit applications cannot communicate with the 32-bit Jet drivers running in WOW64.
The comment from the user “zolten” explains my problem:
Office 2007 x86 already installed on a Windows 7 x64 and an application needs data from an Access db file. The Office driver can’t be installed because of the Office architecture/version conflict.
The solution “offered” by Microsoft is to move to another engine: Microsoft SQL Server Express Edition or Microsoft SQL Server Compact Edition. Neither of both convince me
The problem with SQLite on Windows
And … why not move from MS Access to SQLite? I’d do it, but …. nthere are no drivers to work with Visual Basic 6 (or at least, the ones I tried were too complicated!), and the problem I am trying to solve requires to program with both: VB6 y Python.
To rely on Microsoft is starting to give me headache, and the consecuences are: I haven’t started a new project using C#, and I am testing some Linux distributions. If I add the fact that Microsoft leaves VBA bleeding to death, …. my days as a Windows user can be numbered!
Have a nice day!