Converting MDB Files to SQLite Using VBA: A Practical Guide
Written on
Chapter 1: Introduction
In this guide, I will share segments of code utilized to develop an application that transforms MDB (Microsoft Access 2000) files into SQLite databases. While this may not be a widespread issue, documenting the process serves as a helpful reference for future endeavors.
Chapter 2: Setting Up the Interface
I will skip over the interface creation, as I believe it's straightforward enough to replicate independently.
Section 2.2: Selecting the File for Export
By clicking "Choose Database," I can select the MDB file for export. After selecting the file, I display the list of tables and typically opt to export the entire database. I then use the source database name to set the target database name:
' Code for selecting a file
However, the process of selecting a file is more complex than it appears:
The challenge lies in the fact that MS Access 2000 lacks a straightforward file selection method. A specific function, GetOpenFile(), is needed for this task. While I won't disclose the entire code (as it spans about 300 lines), I have uploaded GetFile.bas to GitHub. This code, originally authored by Ken Getz, dates back to the late 1990s.
After successfully selecting the database, I can derive the name for the target database:
' Code to extract target database name
To display the tables in the source database, I break the task down into two parts: identifying the table names and presenting them on screen. The guide "Listbox Add/Remove Item AC2000" provides insights into filling a listbox. In essence, I can set the RowSource property by passing a semicolon-separated list of names:
MyList.RowSource = "Table1;Table2;Table3".
To retrieve the table names, I must establish a connection to the database:
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = OpenDatabase(pathDatabase, False)
Then, I can utilize the TableDefs collection (DAO) to extract the table names:
For Each tdf In db.TableDefs
Debug.Print tdf.Name
Next
I exclude system and temporary tables from the list to avoid clutter:
For Each tdf In db.TableDefs
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
Debug.Print tdf.NameEnd If
Next
Combining these components, I create the ShowListTable function:
' Code for ShowListTable
To select all tables in the list box, I implement a function provided by Allen Browne.
Section 2.3: Choosing the Destination Folder
Selecting a destination folder may seem simple, but it poses its own challenges, particularly in the late 1990s.
The BrowseFolder function facilitates the opening of a dialog box for folder selection. The original code is attributed to Terry Kreft, and I've shared a copy of this code in GetFolderName.bas on GitHub.
Chapter 3: Creating the SQLite Database
The export process comprises two distinct steps:
- Creating a new SQLite database.
- Populating it with tables from Microsoft Access.
The easiest method to create a new SQLite database involves starting from an existing empty one, copying it to the destination folder, and renaming it.
To verify the existence of a file, I employ a straightforward function:
' Code to check file existence
Similarly, I must ensure that the destination folder exists, which is handled by the CreateFolder function:
' Code for CreateFolder
To copy a file, I utilize FileCopy filePath, destinationFile, and to remove any previous files with the same name, I use Kill destinationFile. By integrating these snippets, I formulate the CopyAFileDeletingOld function:
' Code for CopyAFileDeletingOld
Chapter 4: Exporting the Tables
The function responsible for exporting the tables is detailed below:
' Code for exporting tables
The updateMessage function helps log messages but does not contribute to the export process:
' Code for updateMessage
The crucial element is the ExportFromOtherDatabaseToSQLite dbAccess, nameTable, destinationFile function, which takes the source database location, table name, and target database location as inputs.
To streamline the process, I import the tables I need to export using:
DoCmd.TransferDatabase acImport, "Microsoft Access", dbAccess, acTable, table, table, False
Once the export is complete, I delete the table using:
DoCmd.DeleteObject acTable, table
The ExportToSQLite table, dbSQLite function identifies the table and exports it to dbSQLite.
In conclusion, this project has been a fascinating journey, prompting me to explore challenges rooted in decades past. I found the experience enlightening as I navigated the limitations of MS Access, although it was occasionally frustrating to devise workarounds for issues that seem trivial today.
Thanks for taking the time to read! Stay tuned for future articles, and don’t forget to subscribe to my Medium email list for updates.
This video demonstrates how to connect to a SQLite database using VBA in Excel, offering practical tips and insights.
This video covers the process of converting MDB files to SQLite, showcasing the necessary steps and code examples.