Chapter 1: Introduction to MDB and SQLite

If you're wondering how to transform an MDB file into a SQLite database, the simplest method involves creating an MDB database to facilitate a semi-automated conversion process for other MDB files. Here's an overview of the steps you'll need to take.

To begin, you’ll need to set up your PC. The first task is to download SQLite, which can be accomplished quickly through the precompiled files available at sqlite.org. For Windows users, the appropriate download is sqlite-tools-win32-x86. Once you unpack the folder, you will find three essential files: sqlite3.exe, sqldiff.exe, and sqlite3_analyzer.exe.

Using sqlite3.exe in the terminal, you can create a new empty database. However, it's important to note that Microsoft Access does not natively support SQLite files. Therefore, you'll need to install drivers to bridge this gap. You have two primary options for ODBC drivers:

1. ODBC Driver for SQLite by Devart, which comes at a cost of $170.
2. SQLite ODBC Driver by Christian Werner, though it's limited to SQLite version 3.32.3.

For those seeking free alternatives, I recommend installing the SQLite ODBC Driver.

After establishing an empty SQLite database, you can begin exporting tables from your MS Access database. Open your Access file, navigate to the list of tables, select the desired table to export, and then right-click to choose the Export option.

When prompted, select the ODBC Databases file type, assign a name to the exported table, and choose the appropriate driver for the export. In this case, the SQLite3 Datasource should work perfectly. Then, select your SQLite database using the Browse… button, configure the parameters, and click OK to finalize the export of the table from Access to SQLite.

While manually exporting tables may be feasible for a small number of databases, it becomes impractical with larger datasets comprising dozens, hundreds, or even thousands of tables. To address this challenge, I've developed a solution that automates the process of exporting an MS Access database to a SQLite database. You can find the code uploaded on GitHub in the repository el3um4s/how-to-export-mdb-to-sqlite-3.

To get started, download the following two files to your PC:

• ExportMDBToSQLite3.mdb
• NewSQLiteDB.db

For ease of use, place them in the same directory. Next, open the mdb file, select the source database, designate the destination folder, choose the tables for export, and specify the destination file. Clicking the Export database button will initiate the transfer of the selected tables. Depending on the size of the data and your PC's processor, this might take a few minutes for each database. Nonetheless, this method is significantly more efficient than manually exporting each table one at a time.

For those looking to automate this process further, you might consider writing your own code. I plan to delve deeper into the VBA (Visual Basic for Applications) aspect of this application in my next article.

Thank you for reading! Stay tuned for upcoming insights. Don't forget to subscribe to my Medium email list for updates.

In the first video titled "Mdb to Sqlite", you'll find a visual guide on converting MDB files to SQLite databases, highlighting the necessary steps and tools for a successful transfer.

Chapter 2: Advanced Techniques for SQLite Conversion

The second video, "Sqlite to Mdb", explores the reverse process of converting SQLite databases back to MDB format, offering valuable tips and tricks for effective data management.