Split Database in MS Access

 

What is a Split Database in MS Access?

A split database in MS Access is a database architecture where the database is divided into two separate files:

1. Front-End (FE) Database – Contains forms, reports, queries, macros, and VBA code (UI).
2. Back-End (BE) Database – Contains tables and data (stored separately).

The Front-End connects to the Back-End using linked tables.

---

Why Split a Database?

Splitting an Access database provides several benefits:
✅ Improves Performance – Reduces network traffic when multiple users access the database.
✅ Supports Multi-User Access – Many users can work on the database without conflicts.
✅ Enhances Security – The back-end file can be placed on a secure server while users access the front-end.
✅ Simplifies Updates – You can modify the front-end (UI) without affecting the data.
✅ Reduces Corruption Risk – Less risk of database corruption due to file locking conflicts.

---

How to Split an Access Database?

Step 1: Use the Database Splitter Tool

1. Open your Access Database (.accdb).
2. Click on Database Tools > Access Database (Split Database).
3. Follow the wizard to split the database.
4. Choose a location to save the Back-End database.
5. After splitting, Access automatically links the front-end to the back-end.

Step 2: Distribute the Front-End to Users

• Copy the front-end (.accdb file) to each user's computer.
• The front-end links to the back-end via linked tables.

---

How to Link or Relink Tables to the Back-End?

If the back-end file moves, you need to relink tables:

1. Go to "External Data" > "Linked Table Manager".
2. Select all linked tables and click "Relink".
3. Browse to the new back-end location and confirm.

Alternatively, you can use VBA to relink tables dynamically:

vba
Sub RelinkTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strPath As String
    
    ' Path to the Back-End database
    strPath = "C:\Path\To\Backend.accdb"
    
    ' Open the Front-End database
    Set db = CurrentDb
    
    ' Loop through linked tables and update path
    For Each tdf In db.TableDefs
        If tdf.Connect <> "" Then  ' Check if it's a linked table
            tdf.Connect = ";DATABASE=" & strPath
            tdf.RefreshLink  ' Refresh the table connection
        End If
    Next tdf
    
    ' Cleanup
    Set tdf = Nothing
    Set db = Nothing
    
    MsgBox "Tables relinked successfully!", vbInformation
End Sub

---

When Should You Split a Database?

🔹 If multiple users need access.
🔹 If the database grows large (50MB+).
🔹 If you want to protect data integrity.
🔹 If you plan to migrate to SQL Server later.

---

Alternative: Using SQL Server as a Back-End

Instead of Access for the back-end, you can migrate tables to SQL Server using the SQL Server Migration Assistant (SSMA). This improves scalability and security.