How to move your data from Access to something free

Import the whole Access database into Base by Zaweba, then rebuild the forms and reports you use. Base is free software for Windows with no account, and it brings across your tables, field types, records and relationships in one go. It keeps each database in a single file of its own.

Here is how to plan the move so that nothing is lost and the new version is ready before you stop using the old one.

Before you start

Make a list of what the Access database is actually used for. Which tables matter, which forms people open every day, which reports get printed. Most long-lived databases have a few forms nobody touches and several queries that were written once for a single question. You only need to rebuild what is used.

On the PC that will do the import, install the Access Database Engine, which is Microsoft's component for reading Access files. Base needs it to read .accdb and .mdb files, and the import is offered on Windows only.

Bringing the data across

Use Base's Access import and choose the whole database. It brings every table with its field types and records. AutoNumber keys become record ids. Relationships become lookup fields in Base, shown as dropdowns of the other table's text, and cascade deletes that were set in Access are kept.

Your original file is read, not changed, so you can repeat the import later. That is useful if people go on using Access while you build the forms: when you are ready to switch, import again to get the latest records.

Rebuilding forms

For each table, start with Auto form. It makes a complete form and adds a sub-form for each table that looks the main one up, so an invoice form arrives with its lines underneath, and an order lines sub-form shows an Amount column with a total. Then open it in the form designer and adjust: drag fields from the field list (the label and the right control arrive together), move and resize controls, and set the properties.

Form view will feel familiar to Access users. It shows one record at a time with first, previous, next, last and new, and moving to another record saves the one you were on.

Buttons can open forms, run queries, print reports and move between records. Where your Access database had VBA behind a button, Base uses JavaScript in a sandbox, with Access-style functions such as DLookup, DCount, Nz, MsgBox, DateAdd and Format, which makes rewriting the logic less of a leap.

Rebuilding queries and reports

The visual query builder joins tables along their lookups and supports the conditions Access users rely on: Or rows, between, in list, unique values only, parameter prompts in square brackets and crosstabs. Action queries for updating, appending and deleting records are there too. The SQL tab shows what the builder writes, and you can write your own.

Reports group a table or query by a column, with subtotals and a grand total, in portrait or landscape A4, and print or save as a PDF.

Switching over

Finish with a Switchboard (Create > Switchboard), which makes a menu form with a button for every form, report and query, and set it as the start-up form so the database opens the way people expect. Then choose a cut-over day, import the latest data one last time, and tell everyone to use the new file.

One thing Access never offered: Base by Zaweba can export the finished database as a PHP and MySQL website, with logins, if you later want people to use it from a browser.