Microsoft Access tutorial
Splitting a database for sharing - 3
You now have a folder to use for your shared files. In an Access database, the data tables are stored in this folder. Forms and queries are distributed so that all users have their own copy.
To protect the data in database tables, the tables will will only be available on the server computer, with users linking to it. Forms and other database objects are saved in a separate Access file, which is then linked to the server data. This way, database tables are only used when data is saved to the table. The chance of this occurring simultaneously for the same record is minute (remember you enabled record-locking only).
4. Using a COPY of your database (at least until you are sure you have got it right!):
- Open your database and make sure that the database window is active:
5. The database still needs to be split. To do this:
- Select Tools > Database Utilities > Database Splitter
6. This operation must be performed in the shared directory, which I have named dbScreenshots.
- Navigate to your shared directory,
- Name your database and
- Click Split
Once you do this, you will receive the message that the database has been successfully spilt. When you close the Database Splitter screens, you will also see that the tables in your database have arrows beside them:
7. If you check in the folder you nominated as the location for the back end database, you will see that the back-end database there has tables shown as normal, BUT there are no queries, forms or reports in the database. This is what we want. However, to make sure that the links are correct,
- Select Tools > Database Utilities > Linked Table Manager
to display the following dialog. This shows where YOUR tables are stored (NOTE it will probably be different to mine).
You can now distribute the front part of the split database to any computer on the network. The front-end of the database contains all database objects except for the tables. As you saw previously, these are represented by links to the back-end tables. When you copy the front end of the database to your users' computers, make sure to check that table links point to the correct computer and file, using the Linked Table Manager.
Split databases are designed for data entry using forms. The user's computer communicates with the tables on another machine only when a new entry has been completed, so that the new data can be added to the table. If you use the tables for data entry, you will lock the table all the time so no-one else can use it. Using forms, the table is only locked in the microsecond that the data is actually being added, queried or deleted.
About the author: Kathi Stait has twelve years experience as a successful database developer, designing and creating databases for small businesses and government bodies. She has experience with Microsoft Access, MySQL, SQL Server and Oracle databases, and has taught database courses for the past five years.