In this article I report some parts of the code I used to create an application that converts MDB (Microsoft Access 2000) files into SQLite databases. I don’t think it’s a common problem, but it’s convenient for the future me to have notes.
I omit the part related to the creation of the interface, I think it is quite simple to reproduce even without a guide.
Initialize the menu
When opening the main form, I initialize some components:
I clean the list of tables to export by setting the RowSource field to empty.
Forms!Menu!tableList.RowSource = ""
I also clean the log of export operations by setting the logExport field to empty.
Forms!Menu!logExport = ""
I simplify by always using the same source database. I put the database in the same folder as the main program. I derive the location of the application using CurrentProject.path
Choose the file to export
By clicking on Choose Database I choose the MDB file to export. After choosing the file I show the list of tables and select them all (generally I want to export a whole database). Finally I use the source database name to set the target database name:
The function to select a file seems simple enough but it is not:
The problem is that MS Access 2000 doesn’t have an easy way to select a file. To do this you need a special function GetOpenFile(). I’m not reporting the code here, it’s about 300 lines, but on GitHub I uploaded the GetFile.bas. The code is not mine, it was created a few decades ago by Ken Getz, himself the result of code dating back to 1998.
Anyway, after figuring out how to select the database I can get the name of the target database:
I extract the last part of the address with Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "\")) and then replace the extension mdb with db using a code similar to this Left(nameWithExtension, Len(nameWithExtension) - 3) & "db".
To show the list of tables in the source database I have to break down the problem into two parts. The first is to understand how to read the name of the tables, the second how to show the names on the screen.
To understand how to fill a listbox, the post Listbox Add/Remove Item AC2000 is useful. In summary, just pass the list of names, separated by ; to the control through the property RowSource: MyList.RowSource = "Table1;Table2;Table3".
Instead to read the names of the tables I have to establish a connection to the database:
Dim db As DAO.database
Dim tdf As DAO.TableDef
Set db = OpenDatabase(pathDatabase, False)
Then I can use the TableDefs collection (DAO) to extract the table names:
For Each tdf In db.TableDefs
Debug.Print tdf.name
Next
But I don’t care about the names of the system tables or the temporary ones. To avoid adding to the list I can simply filter them:
For Each tdf In db.TableDefs
If Not (tdf.name Like "MSys*" Or tdf.name Like "~*") Then
Debug.Print tdf.name
End If
Next
By combining everything I get the function ShowListTable:
To select all the tables in the list box I use a function written by Allen Browne
Choose the destination folder
Even choosing the destination folder seems a simple thing, but it is not. Or at least it wasn’t in the late 1990s.
The function BrowseFolder is a function that takes care of opening a dialog box to select a folder. The original codex is by Terry Kreft. On GitHub I uploaded the GetFolderName.bas file with a copy of the code.
Create the SQLite database
The export involves two distinct operations:
First I create a new SQLite database, and then I fill it with Microsoft Access tables.
The simplest way to create a new SQLite database is to start from an existing empty one, copy it to the destination folder and rename it.
To copy a file, I must first verify that it exists. To do this I use a simple function:
The same thing for folders; I have to make sure that a destination folder exists. The function CreateFolder takes care of this:
To copy a file I can use FileCopy filePath, destinationFile. To delete any previous file with the same name I can instead use Kill destinationFile. By merging these snippets I can create the function CopyAFileDeletingOld:
Export the tables
The function that deals with exporting the tables is this:
The function updateMessage is a function that updates the log message and does not contribute to the export:
The important part is ExportFromOtherDatabaseToSQLite dbAccess, nameTable, destinationFile. This function accepts as input the location of the source database, the name of the table to be exported and the location of the target database.
To make things easier I import the tables which I then have to export using:
DoCmd.TransferDatabase acImport, "Microsoft Access", dbAccess, acTable, table, table, False
Then, after finishing the export, I delete the table with
DoCmd.DeleteObject acTable, table
The function ExportToSQLite table, dbSQLite looks for the table table and exports it to dbSQLite:
Well, that’s it with that. This project was very interesting because it required me to do research on some issues of a few decades ago. I found it very instructive to confront some limitations of MS Access. And, to be honest, it’s also very frustrating to look for workarounds to solve things that I take for granted today.
