Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 36 ยท Administration and Security

Split Databases and Multi-User Access

Share an Access database safely with several users. Learn the front-end/back-end split, the Database Splitter, record locking modes, conflicts, LDB lock files and performance tips.

One Access file opened by several people on a network works for about a week. Then someone's connection drops, the file is damaged, two people redesign the same form, and the data and the interface get lost together. The standard cure is the split database: the back end holds only tables, the front end holds everything else (queries, forms, reports, macros, VBA), and each user has their own copy of the front end that links to the shared back end. This is the architecture every serious multi-user Access application uses.

Why split

  • Data safety: users never open the back end directly; there is no interface to crash or to alter.
  • Performance: forms, reports and code load from the local PC; only the data crosses the network.
  • Maintenance: you improve the front end and give users a new copy without touching the data, and the back end can be backed up, compacted and moved independently.
  • Growth path: a back end of linked tables can later be replaced by SQL Server (upsizing) while the front end stays almost the same.
flowchart LR
  A["User 1 PC<br/>Front end copy"] --> D[("Back end<br/>tables only<br/>on file server")]
  B["User 2 PC<br/>Front end copy"] --> D
  C["User 3 PC<br/>Front end copy"] --> D

Each user runs a local front end; the shared file contains only tables.

Splitting with the Database Splitter

  1. Back up the database and make sure nobody else has it open.
  2. Database Tools > Access Database (in the Move Data group) opens the Database Splitter.
  3. Click Split Database and choose a name and a shared network folder for the back end (use a UNC path such as \\fileserver\data\Sales_BE.accdb, not a mapped drive letter, because letters differ between PCs and fail for scheduled tasks).
  4. Access moves the tables into the new back end and replaces them in your file with linked tables; your original file is now the front end.

Distribute the front end (ideally an ACCDE) so that each user has a local copy, for example in C:\Users\<name>\AppData\Local\SalesApp. Never have two people run the same front end file from the server: that recreates the original problem.

Things that stay in the back end: tables, relationships, and table-level properties (validation, defaults). Things that belong in the front end: queries, forms, reports, VBA, macros and temporary tables. Put any table the user changes locally, such as settings or temp work tables, in a small local table in the front end so that two users do not overwrite each other's. Linked-table connection strings are described in linked tables and ODBC.

Record locking

When two people edit the same row, Access decides who wins using record locking, set in File > Options > Client Settings > Advanced > Default record locking (or per form via the Record Locks property):

Mode Behavior
No Locks (optimistic) nobody is blocked while editing; the conflict is detected at save time. Default and recommended.
Edited Record (pessimistic) the row is locked as soon as someone starts editing it; others see a lock indicator until it is saved
All Records locks the whole recordset the form is bound to; use only for rare exclusive jobs

With No Locks, if user A and user B edit the same record and both save, the second sees the Write Conflict dialog with three choices: Save Record (overwrite A's change), Copy to Clipboard (keep B's values to look at) or Drop Changes (accept A's). Teach users to choose carefully. You can also handle conflicts in code in the form's Error event (error 7787 is the write conflict). A simple optimistic technique for important edits is a version column: each row has a number that your update increments, and the update only succeeds if the number is unchanged:

SQL Optimistic locking with a version number

Press Run (or Ctrl + Enter) to see the result here.

When zero rows are updated, the application tells the user "this record was changed by someone else", reloads it and lets them decide.

For forms, set Record Locks = No Locks and keep forms bound to small recordsets: a form bound to a 500,000-row table over the network is slow and holds many locks. Bind to a query with a filter, or to a table limited by a search box, and open child forms with a Where condition.

The lock file and who is connected

When a database is open Access creates a lock file next to it with the same name and the extension .laccdb (.ldb for .mdb). It lists the users and computers that currently have the file open, one 64-byte entry per connection, and it is deleted when the last user closes. A leftover .laccdb after a crash is harmless in itself (Access reuses or deletes it) but it shows that someone may still be connected, so check before you compact. The lock file also limits the number of concurrent users (255 per file, with practical limits far lower, typically 10 to 25 active editors).

From VBA you can read the connected users with the ADO Schema User Roster or by reading the .laccdb. More commonly, add a small heartbeat table the front end updates at start-up and exit so administrators can see who is in the system:

SQL Stand-in: a who-is-connected table

Press Run (or Ctrl + Enter) to see the result here.

Performance tips for networked Access

  • Keep the back end on a wired LAN or a fast server; avoid Wi-Fi, VPNs and cloud-sync folders (OneDrive, Dropbox) for the live file.
  • Open a persistent connection to the back end at start-up (a hidden form bound to a small linked table) so that queries do not reconnect each time.
  • Index the fields you search and join; filter in queries; avoid domain functions (DLookup) inside queries, which run per row.
  • Do not bind combo boxes to huge tables; limit their row source with a query.
  • Avoid copying the front end onto the server and running it from there.
  • Keep both files compact (compact and repair).

When user count or data volume outgrow Access (many users, remote access, 2 GB limit, strong security), the next step is a server back end.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What goes in the back end of a split database?

    The back end holds data only; the interface and code stay in each user's front end.
  2. 2. Which record locking mode is the recommended default?

    No Locks lets people work freely and detects conflicts only when two saves collide.
  3. 3. Why use a UNC path for the back end instead of a mapped drive letter?

    \server\share\file works the same on every machine.
  4. 4. What does the .laccdb file indicate?

    It records current connections and is removed when the last user closes the database.

Summary

  • Split every shared database: tables in a back end on the server, a front end copy on each PC.
  • Use the Database Splitter, UNC paths, local tables for temporary or personal data and deploy updated front ends.
  • Prefer No Locks, handle write conflicts, and use bound queries with filters; consider a version column for critical edits.
  • Wired network, persistent connection and compact files keep a multi-user Access application fast.

Next: Upsizing to SQL Server