How to Split Your Access Database Before Migrating to .accdb

If you're running a multi-user Access database on a network share and you're planning to migrate from .mdb to .accdb, there's one step you should take first: split the database. Skipping this step doesn't necessarily break your migration, but it leaves you with a worse architecture on the other side — and it's much harder to split after the migration than before it. For the full end-to-end migration process, see our complete guide to Access database migration.

This guide walks through what splitting is, why it matters for the .accdb migration specifically, and exactly how to do it using Access's built-in Database Splitter Wizard.

What "splitting" means and why it matters

An unsplit Access database is a single file that contains everything: the tables (data), plus the forms, queries, reports, macros, and VBA code (the application layer). When multiple users open that single file from a network share, Access has to manage simultaneous reads and writes to the same file. This is the source of most multi-user performance and locking issues.

A split database separates the data from the application:

• Back-end file — contains only the tables. Lives on the shared network drive. One copy, shared by all users.
• Front-end file — contains forms, queries, reports, macros, and VBA. Each user runs their own copy, usually from their local machine.

The front-end connects to the back-end via linked tables. Users interact with the front-end locally; the data still lives centrally. Network traffic drops dramatically because only data rows travel across the wire — not forms, code, or other application objects.

Why split before migrating — not after?

You can split at any point, but doing it before the .mdb to .accdb migration gives you two advantages:

1. You migrate a smaller file. The back-end contains only tables — no forms, no code, no queries. It's the part most likely to migrate cleanly. The application layer (front-end) can be migrated separately and tested independently.
2. You isolate failure modes. If something goes wrong during migration, you know which half broke. Did the data migrate correctly? Test the back-end. Did a form stop working? That's a front-end issue. With a monolithic file, everything breaks together and untangling the cause is harder.

Step 1: Back up first

Copy the .mdb file to a location outside the network share before touching anything. This is your rollback point. Name it something like MyDatabase_backup_YYYYMMDD.mdb so you can find it later.

Also make sure no other users have the database open. The Splitter Wizard needs exclusive access to the file.

Step 2: Run the Database Splitter Wizard

Open the .mdb file in Access (not the backup — the working copy you want to split).

1. Go to Database Tools on the ribbon
2. In the "Move Data" group, click Access Database
3. The Database Splitter Wizard opens. Click Split Database
4. Access asks where to save the back-end file. By default it suggests the same folder with a _be suffix (e.g., MyDatabase_be.mdb). Choose the shared network folder where all users can reach it.
5. Click Split

The wizard moves all tables to the back-end file, then creates linked table shortcuts in the front-end that point to the back-end's location. When it finishes, the original .mdb file is now your front-end — lighter, application-only, and linked to the back-end.

Step 3: Verify the split worked

After the wizard completes, look at the Navigation Pane in your front-end. All your tables should still be listed, but each one will have a small arrow icon overlaid on the table icon — that's the indicator that it's a linked table, not a local table.

Open a few tables in Datasheet view and confirm your data is there. Run any forms that perform data entry or queries that read from multiple tables. If everything behaves the same as before, the split worked.

If a table link is broken (you'll see a red "x" icon or an error when you try to open it), use the Linked Table Manager to repair it: Database Tools → Linked Table Manager. Select the broken links and point them to the correct back-end file.

Step 4: Distribute the front-end to users

Now that you have a split architecture, each user should run their own copy of the front-end file. The typical approach:

1. Put the front-end file in a shared location (or create a simple batch script/shortcut) so users can copy it to their local machine
2. Each user opens their local copy of the front-end, which connects to the shared back-end over the network
3. When you update the application (forms, code, queries), you distribute a new front-end — users replace their local copy

This is the correct multi-user Access architecture. It's more work to set up than a single shared file, but the performance and stability difference is significant — especially with 5+ concurrent users.

Step 5: Migrate the back-end to .accdb

Now you have a clean back-end file that contains only tables. This is the file to migrate from .mdb to .accdb first.

Convert the back-end using the Access Migration Assistant (Database Tools → Move Data → Access Database for .accdb) or a dedicated migration tool. The back-end migration is the lowest-risk part: no forms, no code, just tables and their relationships.

After conversion, verify:

• Table structure matches — same field names, data types, and relationships
• Row counts match — no records dropped during conversion
• AutoNumber fields reset correctly (they should continue from where they left off, not reset to 1)
• Any indexed fields are still indexed

Step 6: Relink the front-end to the new .accdb back-end

With the back-end now a .accdb file, the front-end's linked tables still point to the old .mdb back-end. You need to update the links.

Open the front-end in Access and go to Database Tools → Linked Table Manager. Select all the linked tables (or click "Select All"), check the "Always prompt for new location" checkbox, and click OK. When prompted, navigate to the new .accdb back-end file.

Access updates all linked table connections at once. Test the application again — open forms, run queries, try data entry. If everything works, you've successfully migrated the data layer.

Migrating the front-end to .accdb

The front-end migration is usually the more complex half because it contains your forms, VBA code, and queries — all the things that can have compatibility issues between Jet and ACE. Common issues to watch for:

• VBA library references: Check Tools → References in the VBA editor for any [MISSING] entries and re-link them
• DAO vs ADO ambiguity: If both libraries are referenced, qualify your Recordset and Database declarations explicitly
• 32-bit to 64-bit: If moving to 64-bit Access, update any Declare statements to use PtrSafe and LongPtr
• ActiveX controls: Any embedded ActiveX controls may need to be re-registered or replaced

If your front-end is complex — many forms, significant VBA, embedded reports — this is where a thorough pre-migration audit pays for itself. Understand what you have before you migrate it.

What about the .accde / runtime option?

Once your front-end is migrated and working as a .accdb, you can compile it to an .accde (Access Compiled Database Extension) for distribution. An .accde doesn't allow users to see or modify forms, reports, or VBA code — it's the read-only compiled version. This is the right choice for production deployments where you don't want users poking around in the application layer.

Note: You can only create an .accde from a .accdb — not from a .mdb. Another reason the migration is worth doing.

When the database is too complex to split manually

Some databases are genuinely hard to split cleanly. Issues that complicate the process:

• Local tables used as temp/staging tables inside the application (these shouldn't go to the back-end)
• Tables with circular relationships that the Splitter Wizard has trouble handling
• VBA code that uses hard-coded file paths to open the database directly
• Databases that were partially split before and have a mix of local and linked tables

In these cases, a done-for-you migration is often faster than working through the edge cases manually. The split-and-migrate process for a complex database can involve several rounds of testing and adjustment, and having someone who's done it before shortens the cycle significantly.

← Back to all posts