To split an MS Access database, open it in Access for Windows, make a backup, then choose Database Tools > Move Data > Access Database and follow the Database Splitter wizard. The wizard moves the tables into a shared back-end file and leaves forms, queries, reports, and linked tables in a front-end file.
What splitting an Access database does
An Access database often contains both the data and the objects people use to work with that data. Tables store records; forms provide data-entry screens; queries retrieve or change data; and reports present information. If several people open one file that contains everything, they are all using the same file for both the interface and the data.
Splitting separates those roles. The back end contains the tables and is usually stored in a shared folder on a local network. The front end contains the forms, queries, reports, macros, and other application objects. Its table entries become links pointing to the tables in the back end. For example, a small service business might keep its customer and job tables in one shared back end while giving each employee a separate front end with the same forms and reports.
The split does not divide one table into smaller tables, nor does it make Access a web-based or server-hosted database. It is a way to let multiple users work with shared data while keeping each user’s application objects in a separate file. It can reduce some file contention and make updates to the front end easier, but it does not remove Access’s file-size, network, or multiuser limits.
What to prepare before splitting
First, make a separate backup of the original database and confirm that it opens without errors. Keep the backup unchanged until you have tested the split. If the database contains important business records, choose a quiet time for the change so users do not edit data during the process. Close other open copies of the database before running the wizard.
Decide where the back end will live. For a typical small workgroup, that is a folder on a reliable Windows file server or a computer acting as a file server on the same local network. Choose a stable shared path, preferably a UNC path such as \\ServerName\AccessData\, rather than a drive letter that might map differently for each person. Users need appropriate read and write access to the folder, not just permission to open the database file; Access creates a locking file in the same folder while users have the database open.
Also plan how each user will get the front end. The usual arrangement is one local front-end copy per person, saved on that person’s computer. Do not have several people open the same front-end file from a shared folder. Before a real rollout, test with a copy of the database and representative users, forms, queries, and reports.
How to split the database in Access
- Open the database in the desktop version of Microsoft Access for Windows. If it is currently in use, ask other users to close it first.
- Make and verify a backup. Work on the intended database copy, not on the only copy of your data.
- On the ribbon, select Database Tools > Move Data > Access Database. This opens the Database Splitter wizard. The command may be labelled or placed differently in some older Access releases, but it is the built-in database-splitting tool.
- Follow the wizard’s prompt to choose a name and location for the back-end database. Save it in the shared folder you prepared. Access commonly suggests a name based on the original file with a suffix such as
_be; check the path and name before continuing. - Let the wizard complete the split. It moves the tables to the back end and leaves linked versions of those tables in the front end. When the process finishes, note where Access saved each file.
- Open the front end and test it. Check that its tables show as linked tables and that common forms, queries, reports, and data-entry tasks still work. Enter a test record only if it is safe to do so, then confirm that the record appears through the other relevant forms or queries.
The wizard does not automatically create a separate front end for every employee or deploy it to their computers. The front end left by the split is your starting copy. Make a distribution copy for each user after testing, and keep a controlled master copy for future changes.
How to distribute and update front ends
Copy the tested front end to a local folder on each user’s computer. Each copy should point to the same shared back end. If you make a change to a form, report, query, or other front-end object, update the master copy, test it, and distribute the revised front end. This avoids users working with different application versions and reduces the risk of someone editing the master accidentally.
Keep front-end and back-end names and locations stable where possible. If you move or rename the back end, the existing links may no longer resolve. In Access, use External Data > Linked Table Manager to select linked tables and update their source location. The exact prompts can vary by Access version. If you add a new table to the back end later, make sure it is linked into the front end as well; the original split does not automatically link tables added afterward.
For a very small group, distributing the updated file manually may be practical. With more users, an administrator can use a controlled deployment process, but should still ensure that users are not running the old front end during an update. Keep a dated backup of both the front end and back end before making structural changes.
Set up the shared back end safely
Store the back end on a dependable file share that users reach over a stable local network. Access relies on file sharing and locking; a dropped connection while a user is writing data can cause errors or, in some cases, database damage. Avoid treating a cloud-sync folder as a live multiuser file server. Services that synchronize copies in the background can create competing versions or interfere with file locking. A shared folder accessed through a proper network file share is a different arrangement from syncing a folder to each computer.
Give users only the folder and file permissions they need to use the database, and restrict access to people who should see or change its data. Access file splitting is not a substitute for robust server-side security: users with access to the back-end file may be able to inspect or copy its contents. If the data is sensitive or the application needs stronger centralized controls, consider a database server rather than relying on a shared Access file.
Access database files have a maximum size of 2 GB per file, including system objects, so splitting does not mean the back end can grow without limit. Monitor its size and keep regular backups. The suitability of a shared Access back end depends on the number of users, how often they write data, network quality, and the application’s design; there is no single user count that guarantees good performance.
Common problems and how to fix them
- Access says it cannot find a linked table. The back end may have moved, been renamed, or be unavailable to that computer. Confirm the shared folder is reachable, then update the links with Linked Table Manager. Check that all users can access the same path.
- One computer works but another does not. Compare the linked-table path and folder permissions. A mapped drive such as
Z:may refer to different locations or may not be mapped for every user; a consistent UNC path can help avoid that mismatch. - Users overwrite each other’s work or see confusing edits. Review how forms and tables handle simultaneous edits, and avoid having everyone use one shared front-end file. Access uses record locking, but it cannot prevent every conflict caused by application design or overlapping edits.
- The database reports that it is in use or cannot create a locking file. Check that users have the necessary permissions on the containing folder, and confirm that another user has not left the database open. Do not delete a lock file while the database is actively in use.
- The database becomes slow or unstable. Check network reliability, database size, and whether users are opening local front ends. Back up the files before using Access maintenance tools or changing table structure. Persistent problems may indicate that a server-based back end is a better fit.
When to choose another approach
Splitting is a sensible next step when a desktop Access application works well for a small workgroup but currently has its forms and data in one shared file. It is not a complete redesign, and it does not turn Access into a browser application. If users need access from different locations over the internet, frequent concurrent updates, stronger centralized security, or capacity beyond a file-based database, consider moving the tables to SQL Server or another suitable database service while retaining Access as a front end if appropriate.
For a single person using a database on one computer, splitting may add unnecessary link and deployment work. For teams using Microsoft 365, do not assume that storing an Access file in OneDrive or a SharePoint document library makes it safe for simultaneous editing as a back end. Evaluate the actual sharing and storage requirements before choosing a location. Access is available as a Windows desktop application; there is no Access desktop version for macOS, so Mac users need a different way to use the application.
Frequently Asked Questions
Does splitting an Access database create two files?
Yes. The process creates a back end containing the tables and leaves a front end containing the application objects and links to those tables. You may then make separate local copies of the front end for users.
Can multiple users use a split Access database at the same time?
Yes, users can work with a shared back end at the same time over a suitable network. How well it performs depends on the design, activity, network, and number of users. Splitting helps organize multiuser use but does not guarantee that every workload will be reliable or fast.
Should the front end or back end be shared?
Normally, share the back end on the network and give each user a local front-end copy. Avoid having everyone open one shared front-end file, because shared application files complicate updates and can add contention.
Can I split an Access database on a Mac?
No. Microsoft Access is a Windows desktop application, and its Database Splitter is used from Access on Windows. A Mac user would need access to a Windows environment or a different database solution.
What if I move the back-end file later?
Update the front end’s linked-table locations using External Data > Linked Table Manager. Make sure each user’s front end points to the new shared path, then test the application before resuming normal work.
Related
- R Split a String? Methods, Examples, and Common Issues
- Arduino String replace(): Syntax, Examples, and Tips
- Split a Cell in Google Sheets: What Actually Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Custom Label Template in Word: Create and Save Your OwnTo create a custom label template in Microsoft Word, open Mailings > Labels, choose Options > New Label,…
- Excel Keyboard Shortcuts: Essential Keys and TipsFor Excel for Microsoft 365 on Windows, some of the most useful keyboard shortcuts are Ctrl+C to copy,…
- How to Color Values in Excel with Conditional FormattingTo color values in Excel, select the cells, then choose Home > Conditional Formatting to apply a color…
- Excel Import Multiple CSV Files: A Practical GuideTo import multiple CSV files into Excel for Microsoft 365 on Windows, put the files in one folder,…
- How to Freeze X and Y Axes in Excel at the Same TimeTo keep a worksheet’s top row and leftmost column visible while you scroll, select cell B2, then choose…
- Archive All Emails in Outlook: Bulk Methods and LimitsTo archive a large number of Outlook emails, select messages in a folder or search results and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.