How to Split an MS Access Database: Front-End vs Back-End

If more than one person opens your Access database at the same time, and it lives as a single file on a shared drive, you're sitting on a problem that hasn't shown up yet. Corruption, slowdowns, and locked records are all far more likely in that setup. The fix is a standard practice called splitting: separating your database into two files that do different jobs.

I get asked about this constantly, usually right after something has gone wrong. So let me explain what splitting actually means, why it matters, and how to do it properly.

What splitting a database actually means

An unsplit Access file holds everything in one place: your tables (the data) and everything you interact with (forms, queries, reports, and the code behind them). When you split it, you divide that single file into two:

• The back-end holds only your tables. This is your actual data, and it lives on the shared server or network folder.
• The front-end holds the forms, queries, reports, and buttons. Each person gets their own copy on their own computer, and it connects back to the shared tables.

Nothing about how the database looks or works changes for the people using it. They open the same forms and see the same data. The difference is entirely in how the files are arranged behind the scenes.

Why this matters for a shared database

The single biggest reason to split is stability. When five people share one Access file over a network, every keystroke and every screen refresh travels back and forth across that connection. The file is under constant strain, and a dropped connection or a computer that freezes mid-edit is a common way to corrupt the whole thing.

Splitting reduces that risk sharply. Because each person runs their own front-end locally, only the data itself is shared. Less passes over the network, and a crash on one machine no longer threatens everyone else's session.

If you have two or more people using the same Access file on a shared drive and it hasn't been split, treat that as urgent. It's the most common cause of the corrupted databases my team gets called in to recover.

There's a practical maintenance benefit too. When I need to fix a bug or add a new report, I can hand you an updated front-end without touching your data at all. Everyone swaps in the new file and carries on. In an unsplit database, any change means wrestling with the live data file while people are trying to work.

Before you split: back up first

This step is not optional. Copy your entire database file to a safe location before you do anything else. Splitting is generally reliable, but if the process hiccups or you change your mind, you want a clean original to fall back on. Close the database, copy the file, and store the copy somewhere separate.

How to split an MS Access database step by step

Access has a built-in tool for this, and for most databases it does the job well. Here's the sequence:

1. Open the database you want to split, with everyone else out of it.
2. Go to the Database Tools tab on the ribbon.
3. Click Access Database in the "Move Data" group. This launches the Database Splitter wizard.
4. Click Split Database when the wizard appears.
5. Choose where the back-end file will live. This should be the shared network folder that everyone can reach, not a local drive.
6. Let the wizard finish. It creates a new back-end file (usually with "_be" in the name) and turns your original file into the front-end, with the tables now linked to the back-end.

Once that's done, the file you started with is your master front-end. You then distribute a copy of it to each user's computer. Every copy points at the same back-end on the server, so everyone works with the same data.

Getting the front-end to each person

Don't have people open the front-end from the shared drive. That defeats half the point of splitting. Put a copy on each local machine, and give people a desktop shortcut to their own copy. When I set this up for clients, I'll often add a small routine that copies a fresh front-end down automatically whenever I release an update, so nobody has to think about it.

A few things that trip people up

The wizard makes splitting look simple, and often it is. But a handful of issues come up often enough to mention.

Broken links after the back-end moves. The front-end remembers the exact path to the back-end. Move or rename that file, and every front-end loses its connection. If your server path changes, the links have to be refreshed using the Linked Table Manager.

Performance that doesn't improve. Splitting helps stability more than raw speed. If your database is slow because of poorly built queries or missing indexes, splitting alone won't fix that. Those are separate jobs.

Hitting Access's ceiling. A back-end file is still capped at 2 GB and still struggles past roughly a dozen simultaneous users. Splitting buys you room and reliability, but it isn't a substitute for a real database server when you outgrow Access.

Split Access vs SQL Server back-end

People sometimes ask whether they should split or just move everything to SQL Server. They solve different problems at different scales, so here's a plain comparison:

For most small offices, splitting into an Access front-end and back-end is the right first move. It costs nothing, solves the immediate stability problem, and keeps your options open. If you later outgrow it, the front-end you already have can usually be repointed at SQL Server without rebuilding everything.

Is it worth doing yourself?

If your database is small and straightforward, the wizard may be all you need, and I'd encourage you to try it after taking that backup. Where it gets fiddly is distributing front-ends cleanly, keeping links stable when servers change, and making sure nothing breaks for the people relying on it every day. That's usually a couple of hours of work at $30 an hour, and it's a lot cheaper than recovering a corrupted file after the fact.

If you'd rather have someone handle the split properly, set up the front-end distribution, and check the whole thing over first, that's exactly the kind of job my team does regularly. Send me a note about how your database is set up and how many people use it, and I'll tell you honestly whether it's a quick fix or something worth a closer look.