I want to move some data from an existing Access database into a SQLite3 database such that I can access it from an AIR app. Trouble is, it wasn’t easy to find a lot of good tutorials on migrating to SQLite from Access. Here are my notes on my progress (I’m on Windows XP) …

Download SQLite for windows

I downloaded the SQLite application and put in C:\sqlite3. I then added this directory to my machine’s path. In case you don’t know how to do this:

• Right-click computer
• select properties
• select environmental variables button
• select the “Path” entry under System variables
• add ;C:\sqlite\sqlite3.exe to the end of the text string (or whatever the path is to your sqlite3 executable

Download ODBC for SQLite

Next, I needed to install an ODBC wrapper for SQLite so that MS Access knows how to export the existing database to SQLite3. Found a nice tool at http://www.ch-werner.de/sqliteodbc/

Running the installer on this program does everything you need to get the correct ODBC settings setup on your machine. You can prove this by going to the Data Sources dialog and looking under System DSN

• Go to Start>Control Panel>Administrative Tools>Data Sources and open dialog
• Look in System DSN and SQLite should now be there
• According to this doc from OpenOffice, the UTF-8 driver is experimental

Export from Access 2007

Ok. Now we’re ready to export a table from Access to SQLite. NOTE: I’m using the trial version of Access 2007…not sure how this would work in earlier versions.

• Open up your existing Access database.
• Select the table you want to export (not sure how to dump the entire db…in fact, I’d much rather just dump the whole database to SQL but doesn’t seem like Access allows this)
• Select the External Data tab
• Select Export (more) > ODBC Database
• Type in the name of the table. Access will default to the same name. I left mine as is.

That’s it. There should be a sqlite.db file in the same diretory as your Access database.

To prove that’s the case, you can use Peter Elst’s SQLite Editor (scroll down to middle of page) to open your new sqlite db. Just make sure your .db file is in the bin-debug folder and that you change the file attribute in the SQLite_schema.xml file on line 44 to the name of your file.

<sqlite:SQLite id="test_db" file="sqlite.db" open="getDatabaseSchema(event)" />

Exporting whole database

Now call me crazy but I would assume a lot of people would want to export an entire database from Access to SQLite. But I can’t seem to find this operation in the Access 2007 menu structure. I can only export one table at a time. Anybody have better luck?

There does seem to be a VBA command called TransferDatabase, but it too doesn’t have the option to do what it’s name suggests, which is transfer an entire database in one go.

Furthermore, Access won’t let me just dump the entire db to SQL commands (I think this is called a SQL projection). So, as of now the only solution I can see is to step through each table and export it separately. I doubt this method will preserve any kind of key relationships. I’ll write more as I move along.

(BTW, there’s a free SQLite browser on sourceforge if you want to browse your db quickly: http://sourceforge.net/projects/sqlitebrowser/ )

Galen, a Ruby on Rails friend of mine has essentially memorized most of the commands that make TextMate so powerful. Have you ever seen a rails whiz work TextMate? Genious. 

Flex Builder is kind of a dump truck to TextMate’s lightweight racing frame, but it can do some pretty neat stuff. I just learned a six new tips in ten minutes in this video by Adobe.