There is a problem that has been bothering me for some years. I have a few hundred databases in mdb format (Microsoft Access 2000) full of data from a quarter of a century, and I don’t want to lose them. This is a complex problem, because it concerns several issues. The databases are still in use, so I can’t just replace them. Furthermore, being the result of a ten-year accumulation, they are very spaghetti code. Finally, those who use these databases often know almost nothing about computers.
Important: these databases are used by voluntary associations. They often run on old, obsolete, low-memory, low-performance computers. And they are mostly Windows PCs.
Finally, as a last requirement, they must be able to work even in situations where there is no internet connection. Better to be completely offline most of the time. The passage of data from one machine to another must be quick and easy, using USB sticks or CDs.
I’ve tried various solutions, but couldn’t find one that worked.
MDBTools
One attempt was to use mdbtools for linux. However, I ran into limitations in various linux distributions. I also had to look for a version that worked with Windows. I then looked for a way to use mdbtools on both Windows and Linux. After a few tries I have created a packages, NNode MdbTools which works quite well. To be able to read an Access file, simply install the package with the command
npm install @el3um4s/mdbtools
Then, I can get the list of tables in an mdb file using a function similar to this:
I can query the database to get the data of a specific table:
I can also save the query result to a file:
Or directly save a table in a csv file
All very well but, in some cases, it doesn’t work. These are quite specific cases, and they concern the particularity of my mother tongue: accents. Some tables use accented characters. And this misleads some exports and some queries.
NODE ADODB
Another attempt was to use a Nuintun package called Node Adodb . The last update dates back to December 2020 and in the meantime some problems have accumulated. I then forked my updated version (el3um4s/node-adodb).
I install the package
npm install @el3um4s/node-adodb
I can query a database table with this command:
This package works, but only on Windows. Also, it can be very slow. Yes, it is a good solution for operations to be performed once in a while but not for frequent use.
NODE MDB
Then, I tried to recreate this repository from scratch, el3um4s/node-mdb . To install it just write:
npm i @el3um4s/node-mdb
So to get the list of tables I can use
I can query a table by writing queries
Can I export the result to a file
Or edit the table with queries similar to these:
Again, however, the system tends to be slow in some databases. And, anyway, it only works on Windows.
Conclusion
So what? Well, after trying many solutions, and noticing that there are inconsistencies in behavior on Windows, I decided that perhaps the best way is to try to convert the various databases from MDB to SQLite.
This way I can use a package like sqlite3:
npm i sqlite3
I can get the list of tables in a database with a command similar to this
Or query a database using an sql query similar to this one
At the moment this is the best solution. But it is not the solution I was looking for and it creates a new problem for me: how do I convert an MDB file to SQLite? Possibly automatically.
I will talk about this in a future post.
