How to export a database from Access to SQLite: a short guide

Hi all!

In this article I will tell you how I transferred data from MC Access to SQLite.

A little background:

I had a need to export a database from Access (or more precisely from the .mdb format) to SQLite. These DBMSs do not have compatible formats (as it turned out) and I had to figure out how this could be done. And this is what I came up with:

Actually the guide

So we have a .mdb or .accdb (or whatever format Access supports) and we need to get something like .db or .sqlite. I’ll say right away that I didn’t want to consider various online services (even if they exist), since there’s no talk about security and speed, so I had to come up with something myself.

The difficulty was also that my experimental database was with a Window and this made it difficult to export to some formats.

Before we continue, I’ll introduce you to our test database:

MC Access with open chemical database. elements

Author of this database: Ulybysheva Irina Mikhailovna. You can download the original Here.

My version of this database, translated into English and exported to many formats, is at GitHub.

Ok, we are familiar with the experimental subjects, now we can get started. I will say right away that this method may seem a little crutch, but it works. If you have other suggestions on how this can be done, write in a comment to the article or in a telegram to me (the link will be at the end of the article).

Step 1. Open the required database in Access, go to the External Data tab and click Export to Excel. After this, a dialog box will open in which you need to select the directory in which to save the file and the format in which to save it.

Step 2. Open the resulting file in Excel (I hope you don’t need to tell us how). And now we can perform all the necessary manipulations with the table, for example, translate it into another language, etc. When finished, press File > Export > Change File Type and choose CSV.

After that, click Save As, select the path and save.

Step 3 – Final. Open SQLite DB browser. Create a new database in the menu File choose Import and select a sub-item Table from CVS. Select the table we are interested in, configure it and click OK.

And now you can do whatever you want with the new database. This concludes the guide. If you have any comments, write.

From the author:

Thank you for reading the article to the end, I hope it was useful to you and saved you time. If you want to influence the publication of further articles, you can subscribe to my telegram channel, there will also be polls regarding the release of new articles. If you want to contact me or suggest a topic for an article, my contacts are on website.

Thanks again for reading this far!

Good luck!