Split Access Database

Separate tables

You don't need to keep all of your data in one file. You can split your MDB file into data and application files.

"Even if all your data is in Access itself, consider using linked tables. Store all the data tables in one MDB file - the data file - and the remaining objects (queries, forms, reports, macros, and modules) in a second MDB - the application file.
In multi-user situations, each user receives a local copy of the application file, linked to the tables in the single remote data file."

• Maintenance: To update the program, just replace the application file.
  Since the data is in a separate file, no data is overwritten.
  
  
• Network Traffic: Loading the entire application (forms, controls, code, etc)across the network increases traffic making your interface slower.
  

In some cases you will link additional files:

• Static look-up data such as postal codes might be kept in its own file.
  
• Linked temporary tables might avoid the need to compact the application file.
  

From Allen Brown's tips for Access users
Access has a tool to do the splitting for you, go to: Tools>Database Utilities Database Splitter
In Access 2007+:

1. On the Database Tools tab, in the Move Data group, click Access Database.
   
2. In the Database Splitter dialog box, click Split Database.
   
3. Type a name for the back-end database, and then click Split.

Also:
Knowledgebase:
How to manually split a Microsoft Access database

MSDN:
About sharing an Access database on a network  



See all Topics