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.