Expert guide

How to Split a Microsoft Access Database

Back end on the server, front end on every PC: the foundation of a reliable multi-user database

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: back up, then use Database Tools → Move Data → Access Database to move all tables into a back-end file saved on the server using a UNC path. The original file becomes the front end with linked tables. Give every user their own copy of the front end on their PC, distribute updates with a version check, and back up the back end nightly. Never share one front end file over the network. For larger teams, see Access database optimisation.

An Access database that starts as one person's tool often becomes the whole team's system without anyone redesigning it. Five people open the same file on a shared drive, and every few weeks it corrupts, slows down or locks someone out. Splitting the database is the standard fix and takes less than an hour. Doing it properly, with the details below, is what makes it stick.

Front End vs Back End

PartContainsLives onChanges
Back endTables and relationships onlyOne shared location (file server)Rarely: only data changes daily
Front endLinked tables, queries, forms, reports, macros, VBA, local temp tablesEach user's PCWhenever you release new features

Before You Split

  • Get everyone out and make a dated backup copy of the file.

  • Compact and repair the copy you will split.

  • Decide the back-end location: a reliable Windows file share on wired network, not a sync folder, not over Wi-Fi.

  • Find the UNC path of that location, e.g. \\fileserver\Data\Ops\.

  • Check every user has Modify permission on that folder (needed for the lock file).

Step-by-Step Split

  • 1. Run the splitter

    Database Tools → Move Data → Access Database → Split Database.

  • 2. Save the back end using the UNC path

    In the save dialog, type the full UNC path (\\fileserver\Data\Ops\Ops_BE.accdb) rather than browsing through a mapped drive letter, so links are stored with the UNC path.

  • 3. Check the links

    In the front end, linked tables show an arrow icon. Hover or use Linked Table Manager to confirm they point to the UNC path.

  • 4. Move any local-only tables back

    Tables that should be per user (temporary import tables, user settings) can live in the front end instead of the back end.

  • 5. Test

    Open forms, add and edit records, run reports. Then test with two users at once.

Distributing the Front End

Each user needs their own front end. The simplest reliable approach is a master copy on the server plus a launcher that copies it locally when the version changes.

' In the front end: a version check on startup (AutoExec or startup form)
Public Function CheckFrontEndVersion() As Boolean
    Dim localVer As String, serverVer As String
    localVer = DLookup("Version", "tblLocalVersion")               ' local table
    serverVer = DLookup("Version", "tblServerVersion")             ' linked from back end
    If localVer <> serverVer Then
        MsgBox "A new version is available. The database will now update.", vbInformation
        Shell "cmd /c ""\\fileserver\Apps\Ops\UpdateFrontEnd.cmd""", vbHide
        Application.Quit
    End If
    CheckFrontEndVersion = True
End Function
UpdateFrontEnd.cmd (on the server):
@echo off
timeout /t 3 /nobreak > nul
copy /y "\\fileserver\Apps\Ops\Ops_FE.accde" "%LOCALAPPDATA%\Ops\Ops_FE.accde"
start "" "%LOCALAPPDATA%\Ops\Ops_FE.accde"

Users launch the database from a desktop shortcut that points to their local copy. When you release version 2.4, you bump the version in the back end table and copy the new master to the server. Everyone updates automatically the next time they open it.

Performance Tips After Splitting

  • Keep a persistent connection: open a hidden form bound to a small back-end table at startup, so Access does not repeatedly create and delete the lock file.

  • Open forms filtered to what the user needs rather than whole tables.

  • Index fields used in joins and criteria in the back end.

  • Turn off Name AutoCorrect in both files (File → Options → Current Database).

Common Mistakes

MistakeConsequence
Everyone opens one shared front endCorruption and locking come back
Links stored with a mapped drive letterBreaks on PCs with different mappings
Back end in OneDrive/Dropbox/SharePoint syncConflicts and corruption
Users on Wi-Fi or VPN to the back endSlow and corruption-prone; use remote desktop or SQL Server
Design changes made in the back end during the dayLocks and bloat; do it out of hours
No back-end backupsData loss when corruption does happen

Network-related risks are covered in Access corruption over LAN, Wi-Fi and VPN. If the server path changes later, a relink routine fixes every front end; see relinking Access tables with VBA.

When Splitting Is Not Enough

A split Access database comfortably serves a team on one site. Remote users, larger numbers of concurrent users, very large data, or stricter security needs point to a SQL Server back end with the same front end. See the Access 2 GB limit and Access to SQL Server migration, or ask an Access consultant to review your setup.

FAQ

Splitting Access databases FAQ

Front ends, back ends and distribution

Splitting separates the database into a back end that contains only the tables (the data) and a front end that contains queries, forms, reports, macros and VBA, with linked tables pointing to the back end. The back end sits on a shared location; each user gets their own copy of the front end.

Splitting reduces corruption risk, improves performance because forms and code load from each user's PC, lets you update the front end without touching data, and lets each user have their own temporary objects and settings. Multiple users sharing one unsplit file is the most common cause of avoidable corruption.

Yes. Each user should run a front end copy on their own PC (or their own profile on a remote desktop server). Sharing a single front end file over the network brings back most of the problems splitting is meant to solve.

Keep a master front end on the server with a version number, and give users a launcher (a small script or a startup check) that copies the latest version to their PC when the version changes. That way you never ask people to copy files manually.

Use a UNC path such as \\server\share\data\Ops_BE.accdb. Drive letters can differ between PCs and break links; UNC paths are the same everywhere.