Converting an SQLite database to Access

Hello, this blog has moved to here : http://blog.ericgazoni.me, the blog on WordPress will be kept not to break links, but will not be updated anymore, see you there !
SQLite Logo, SVG version

Image via Wikipedia

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.
Advertisements

4 responses to “Converting an SQLite database to Access

  1. Thanks you very much!

    Your method helped me to recover 460 memos from the build-in application of the Samsung Galaxy S GT-I9000 written with SQLITE.
    I saved the file memo.db but don’t know what to do with…

  2. in Access 2010, make sure you select “Machine Data Source” for the DSN when import from external odbc.

    Very fast! This now allows me to convert 15+million rows of data per file to a proprietary system in a reasonable fashion.
    Many thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s