Splitting and Deploying Your Database
So you’ve developed a database and now you are finally ready to deploy it to your users! There are a couple things to consider to simplify your life as much as possible.
Split Your Database
Split? Yes, if you have not done so, and you are setting up a network or multi-user database, it is time to split your database. This is a simple process by which the tables are placed into one database (called the Back-End) and the remaining database objects (queries, form, report, code) are placed into another database (called the Front-End). The Back-End is then placed on the office server and a copy of the Front-End is given to each user and setup on their PC. DO NOT allow your users to all connect using the same Front-End file, they should each have their own copy on their machine.
Why Split your Database
There are numerous reasons/advantages to splitting your database, such as:
- Performance – Since the Front-End is located on each user PC, it can have significant benefits in overall performance.
- Reduced Network Traffic – Since the Front-End is located on each user PC there is that much less data to transfer back and forth over the network.
- Maintenance/Updating – Splitting the database simplifies the process of performing updates. By creating update scripts, you can in effect simply replace the front-end and automatically upload it to your users while never having to take the database offline.
- Reduces Corruption – It has been stated that splitting your database can reduce data corruption.
- Multi-User – Splitting your database permits multiple-user simultaneous usage of your database.
How-to Split Your Db (Access 2000-2003)
Tools->database utilities->Database Splitter
How -to Split Your Db (Access 2007)
Database Tools -> Move Data -> Access Database
Database Splitting References
Split your Access database into data and application
Splitting a access database, or how to run ms-access in a multi-user mode
Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability
Creating a Persistent Connection
Once you split your database, it is very imporant to create a persistent connection between your Front-End (FE) and your Back-End (BE) to ensure optimal performance of your database. To do so:
In your back-end (BE)
- Create a bogus table in your back-end with 1 field, then add 2-3 entries into the table (keep it simple, but the table name and actual data have no importance).
In your front-end (FE)
- Create a new linked table based off of the newly created bogus table (step above)
- Create a form that use's that table as it's recordsource
- In your database startup routine, most probably AutoExec Macro, open the form in a hidden mode as soon as your db launches