Advantages of Using Split Access Databases

One of the main advantages of Access is that it is possible to store both the data as well as the interface objects in the same MDB files. However, most experienced Access developers believe that it is easier to maintain the database when it is split into two MDB files and that this may also help prevent corruption of the files. This means a single Access database file can be split into two files ' one containing the data in the form of relational tables and which acts as the backend; the other stores the interface objects such as forms, reports, etc. and acts as the front end. Of course, users can always view and modify the data stored in the backend file by using the forms and reports stored in the front end. This kind of split database arrangement helps resolve a number of problems that are peculiar to the Access file structure and which would otherwise have ended up damaging the MDB file, possibly requiring a quick MDB repair.

Some of the main advantages of using split Access databases are:

·        It is easier for multiple users to use and manipulate the data stored in the backend since the front end alone can be easily distributed to all the interested users. The backend in the meanwhile can be stored securely on a file server.

·        One of the easiest ways of protecting your Access data is by splitting the database file or the MDB file. The only way users can access the data is by means of the interface objects that have been made available to them in the front end. Users thus, cannot access the relational tables directly and will not be able to modify the tables, even by mistake. This in turn means there will be less damage to the MDB file containing the actual data and there will be a reduced need for MDB repair. On the other hand, users will never realize that they are actually working with two MDB files and so long as they are able to access data smoothly, this arrangement works well for users as well.

·        If there is any possibility that your Access database is going to grow so large that you might consider moving it to a larger relational database system, then spilt databases is the way to go. It is infinitely easy to upsize a split Access database than migrating one huge database file. Further, you can store the backend data in the larger database while continuing to use the Access front end. Even if you plan to have a new, more robust front end for the new back end, you need not worry about interrupting operations but continue to use the Access front end while the new front end is in development.