databasedev.co.uk - database solutions and downloads for microsoft access

Splitting A Microsoft Access Database

Benefits of Splitting Your Microsoft Access Database:

If it is your intent to allow multiple users to access your Microsoft Access database, you should split the database to preserve the integrity of your database files. Splitting the database divides the database such that its data tables are stored in a database separate from the database that contains all other objects and functions, which act upon data tables. Splitting your database allows you to create a client (front-end) - server (back-end) database architecture. The split database contains a single server and one or more clients, one for each database user. Only the database tables are stored on the back-end, server. The front-end, client database contains objects, functions and all other user programming necessary to operate and control the database. Microsoft Access provides methods, which allow clients and the server to communicate with one another, automatically.

The benefit of configuring your Microsoft Access database, using this client-server architecture, is to ensure that the established database, stored at the back-end server, is available to multiple users. The server is, typically, stored at some central location on a computer that is optimised to handle multiple and simultaneous user access. Using this configuration, the server can be used to perform a multitude of services for the client machine without having to, physically, access the client machine.

You may, for example, assign different clients with different levels of permission. You may assign some clients read only permission to your database while assigning others read and write permissions. The server can be used to grant this type of permission to clients. If your clients require remote access to your database, the client-server configuration eliminates performance degradations due to slow networks and computers, which could create bottlenecks and corrupt a single Microsoft Access database.

If you created your database using an older version of Microsoft Access and you choose to migrate to a newer version of Microsoft Access, you should split your database to ensure continued access to the older database files. Different versions of Microsoft Access incorporate different methods of file formatting. The one thing that has remained consistent, however, is the method of reading database tables. If older versions of database tables are placed on the back-end server, front-end clients, using any version of Microsoft Access, can be configured to access and import data from the older data tables. If some of your clients have not upgraded to the new version of Microsoft Access, the established back-ended database remains in tact and available for use by all shared users. The server may be used to configure and update software specific to each client machine.

One of the primary requirements for a split database is that each of your clients knows where the server is located. Also, the server does not have to reside on the same type of platform as the client. The server may reside on a UNIX platform, for example, and have clients running on Windows platforms, configured to access the UNIX server. The client platform, however, must be Windows based with Microsoft Access installed. If the server is moved, each client must, first, be properly updated to point to the new location. The update sequence should occur such that a client is never pointed to a null location.

See Also: How to Split a Microsoft Access Database