So how can I convert an MDB file to a SQLite database? At the end of the day the easiest way is to create an MDB databas,e to semi-automatically convert the other MDB files. I want to get a mask similar to this:
But before I can get to this I have to configure the PC. I start with downloading SQLite. I can do this pretty quickly using the precompiled files on sqlite.org. For Windows I download sqlite-tools-win32-x86. After unpacking the folder I have 3 files available:
- sqlite3.exe
- sqldiff.exe
- sqlite3_analyzer.exe
I can use sqlite3.exe from terminal to create an empty database:
Microsoft Access doesn’t natively recognize SQLite files, I need drivers. There only two alternatives:
- ODBC Driver for SQLite by devart. But it costs $ 170.
- SQLite ODBC Driver by Christian Werner. But it’s stuck at SQLite version 3.32.3
Free drivers are suitable for me. Then I install the SQLite ODBC Driver.
After creating an empty SQLite database I can start exporting the various tables of an MS Access database. I open the file with Access, go to the list of tables, select the table to export. I then click with the right mouse button and select the Export option.
As file type I choose ODBC Databases () as file type
Then I decide the name to assign to the exported table:
Then I select the driver to use for export. In my case SQLite3 Datasource is fine .
Then I select the SQLite database (using the Browse… button ), I set the parameters and then click OK to complete the export of the table from Access to SQLite.
As long as it’s a handful of databases, and a few tables, it’s possible to do everything manually. But when things get bigger, when there are dozens, hundreds or even thousands of tables, you need to use a more automatic option. For this reason, I have created a solution that allows me to export an MS Access database to a SQLite database. I uploaded the code to GitHub in the el3um4s/how-to-export-mdb-to-sqlite-3 repository .
Download to PC two files:
For simplicity I put them in the same folder. Then I open the mdb file:
I can select the source database, the destination folder, the tables to export and the destination file. Clicking the Export database to button starts the export of the selected tables. Depending on the amount of data in the database, and the processor of the PC used, it can take a few minutes for each database. But it’s still easier and faster than doing it by hand table by table.
To automate even more, you can write your own code. But I will talk about this in the next article, where I will go a little deeper into the VBA (Visual Basic for Applications) part of this application.
