Even if all your data is in Access itself, consider using linked tables. Store all the data tables in one MDB or ACCDB 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.
Why split?
There are significant advantages to splitting your application:
- 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.
Access includes an Add-In to perform the split:
Access 95 - 97 | Tools | Add-Ins | Database Splitter |
Access 2000 - 2003 | Tools | Database Utilities | Database Splitter |
Access 2007 - 2010 | Database Tools | Move Data | Access Back-End |
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.
Is the Data present?
Once split, the data may be unavailable to the application. This happens if the network is down, the data file is moved, or folder/network names are altered.
SOURCE | LINK | LANGUAGE | ENGLISH |
good day I read your website and I think you’re blog will be one of the bests if you keep up the good work!