What actually moves when an .mdb file becomes SQLite

What moves into the SQLite file

A conversion carries four things: table definitions, the rows a reader could actually read, primary keys, and the indexes that survived. It does not carry queries, forms, reports, macros, or VBA — those are Access application objects, not database objects, and SQLite has no layer for them. Knowing which half you are moving is the difference between a migration and a data loss you find out about next month.

The other thing worth knowing before you start: SQLite has no exact decimal type and no native date type. Both exist in Access. Every converter has to make a decision about them, and the decision is usually made for you, silently, at the moment the file is written.

Decide first whether the file can be read

Before choosing a converter, establish how much of the file is readable. Two numbers decide everything: how many rows the file’s own catalog says each table has, and how many rows a reader can actually return. When those two numbers match, the file is healthy and any converter will do. When they diverge, the gap is what you are about to lose.

You can get both numbers without installing anything by opening the file in the browser-based Access recovery tool, which parses the file locally and reports, per table, four counts.

1. 1

   Declared in catalog

   How many rows the file itself says the table contains. This is the file’s own number, not an estimate.

2. 2

   Recovered

   How many rows the reader actually returned on this pass. Compare it against the declared count.

3. 3

   Unreadable (skipped)

   Rows the structure points at but the reader could not decode — damaged pages, truncated records, failed integrity checks.

4. 4

   Not reached

   Rows the reader never walked to at all, usually because the page chain breaks partway through the file.

A conversion tool only ever sees the second number. It has no vocabulary for the third and fourth, which is why a converter can report “converted 3 tables, 12,400 rows” on a file that used to hold 18,000. The report is true and the conclusion you draw from it is wrong.

If the header itself is rejected — the file was renamed, truncated, or written by a newer Access build — you get a different failure entirely. The unrecognized database format check explains what those first bytes are compared against, and why a converter cannot get past them.

Type mapping: the values that change shape

Access stores more types than SQLite does, so a conversion has to flatten something. The four that matter in practice are Currency/Decimal, Date/Time, Yes/No, and OLE/Attachment — each has a conventional mapping, and each mapping has a cost you should know before you rely on the result.

The Currency decision is the one to make deliberately. If the columns feed invoices or any arithmetic, store them as integer minor units or as text, and do the arithmetic in your application. A double that looks correct to two decimal places is not the same as a correct number.

AutoNumber, foreign keys, and indexes

AutoNumber columns usually become INTEGER PRIMARY KEY, which in SQLite is an alias for the rowid; new rows continue numbering after the highest value copied. Existing key values are preserved, so relationships that referenced them still resolve — provided the foreign keys themselves are carried across.

Foreign keys are where a migration quietly degrades. SQLite accepts foreign key declarations, but enforcement is off by default and must be enabled per connection with PRAGMA foreign_keys = ON. A converter that writes the constraint without telling you this produces a schema that looks correct and enforces nothing.

Our own engine reports this boundary rather than hiding it. On a 31-file corpus exported to both .mdb and .accdb output (62 artifacts), every file reopened cleanly in an independent Jet reader, with the user table-name sets and per-table row counts matching table by table, and 368 indexes recognised. Two limits are stated rather than smoothed over: 20 foreign-key indexes could not be carried — the cause is page-number relocation, which needs a second pass — and simplified Chinese collation weights are complete for 65 of the collation entries (about 1.1%), while all 63,488 Chinese characters themselves encode correctly.

One structural point that has nothing to do with loss: our export writes Jet 4. A source stored as Jet 3 or as ACE therefore comes back as a different format. That is a fidelity gap in the container, not damage to the data, and it is worth knowing before you promise anyone an identical file back.

Queries, forms, and VBA stay in Access

In Access, the database file holds both the data and the application. In SQLite there is only data. Saved queries, forms, reports, macros and VBA modules therefore have no destination — a converter that claims to move them is either exporting their source as text or mislabeling what it did.

This is usually fine, because those objects were written against Access itself: form event handlers, DoCmd calls, Access SQL dialects with IIf and Nz. Moving them would mean moving Access. What you can reasonably ask for is the source, so the business logic can be read and reimplemented rather than guessed at. Our tool extracts VBA source and saved-query SQL as text attachments — on a reference file with 4 forms, 3 VBA modules, 2 queries and 2 relationships, all of it came out readable.

Verify by row count, not by file size

A conversion is verified when the row count in the destination matches the declared count in the source, table by table, with the unmatched rows accounted for. File size, table count, and a successful exit code are all weaker signals — a file can shrink legitimately after compacting, and a converter can exit zero having skipped a table it could not parse.

Run a query against the result rather than eyeballing it. In our own verification, an exported .sql file of 753,909 bytes built a table that loaded 11,149 rows into SQLite — the same count the page had reported — and an exported SQLite file began with the SQLite format 3 magic string and opened in Node’s built-in SQLite reader with 4 tables and 21 rows. Both checks took seconds and would have caught a truncated export immediately.

• Compare per-table counts, not totals. Two tables can offset each other and leave the total looking right.
• Check one text column and one numeric column against known values. Count agreement does not prove value agreement.
• If a table is missing from the destination, treat it as damage to investigate, not as an empty table.

When converting is the wrong move

Converting is the wrong move when the source file is not fully readable, when you need the Access application to keep running, or when the target is going to be edited by someone who expects Access behaviour. In the first case the conversion does not fail loudly — it produces a smaller, clean, plausible file.

Start with diagnosis. If the file opens read-only rather than failing to parse, the problem may not be damage at all: a lock file, a permissions issue, or a Trust Center block produces a read-only state that looks like corruption and needs none of this machinery. If the counts reconcile, convert. If they do not, recover first and convert the recovered result.

The cost of the wrong order is asymmetric. Converting a damaged file spends an afternoon and produces something that looks finished. Reading it first costs a few minutes and tells you whether the afternoon is worth spending. Our FAQ covers what the free pass reports before any payment, and pricing covers where the export unlock sits.