Converting an SQLite database to Access
by Eric Gazoni
After looking for two hours after a tool able to perform such a conversion for a reasonable price, I found a free solution to my customer request:
- You need Windows, but it works fine in a virtual machine (such as Virtualbox) if you’re on a Mac or Linux/BSD
- You will also need MS Access installed on this Windows box (I’ll be using Access 2007, so the menu labels might not look exactly as I say, but they are here)
- You will need to install the SQLite ODBC driver by Christian Werner
- Then, do not try to use the driver directly from Access (almost gave up the whole thing doing this way) but instead go to your Control Panel > ODBC Sources
- Create a new source (DSN) from here, pointing to your SQLite file (tested with a SQLite3 database, using the SQLite3 ODBC driver)
- Start Access, create a new database (tip: if you want to keep Access 2003 compatibility under Access 2007, replace the .accdb extension by .mdb in the “new filename” box
- Go to External Data > ODBC Sources > System sources and select the DSN you just created at previous step
- You’ll be prompted to select which tables to import, then start import
- You’re done
It did not preserve Foreign Keys at my first trial, I saw an option somewhere during the DSN configuration, but didn’t try it. As the driver is working perfectly, this option might as well.
If it spits a reserved error” or says the database is encrypted or is not a valid database, then you didn’t follow the sequence and are probably trying to add the DSN from the Access ODBC dialog. Remember, it has to be done system wide or it won’t work.