Splitting an Access database is about the most important thing that you can do to add stability to an application and any experienced developer will almost certainly tell you to ‘split’ your database into a Front End (FE) and Back End (BE)
The Front End will contain all the Forms, Queries, Reports, Macros, and VBA code that your users will use to interact with Access, and the data tables are all held in the Back End.
What are the advantages to splitting the database?
In a split database environment it’s normal for the backend to be placed on a central server somewhere, and for individual users to have a copy of the Front End held locally on their PC’s. This gives three big advantages, speed, reliability, and ease of updates.
Speed is usually greatly increased when using a local front end as the time to access the Front End from a local hard drive is obviously much faster than accessing the Front End over a network, this is especially true for large Front Ends that may be many megabytes in size.
With regard to reliability, Microsoft Access is a multi-user aware application that can happily handle more than one user accessing the same Front End concurrently, however in our experience, more than about eight users tends to make the application a little unstable, and more than twelve or fifteen users is almost guaranteed to crash the application. By having a Front End for each user, these issues are virtually eradicated.
Another, important advantage, is that it’s easier to develop and make changes to a running application – to make changes to a Front End, you will need to have exclusive access to that front end, and obviously if you have many users, you may find it difficult to get that exclusive access, or you may have to lock your users out for a period of time whilst you make your changes. With each user having their own Front End, you can make your changes to the Front End, and then distribute new Front Ends to your users at your convenience.
How and when do I split the database?
- If you’re starting a new database form scratch, then we would always urge people to start with a split database, this is done by creating an Access database that contains only the datatables, and then creating a second database that only contains the Forms, Queries etc.
- If your database is already in existence, and needs to be split to solve performance and stability issues, then there is a handy wizard within Microsoft Access that will handle all the splitting etc for you.
Are there any disadvantages to splitting the database?
In theory there are no real disadvantages to splitting your database, however in reality there are a couple of things to consider:
- If you have quite a few users it can be a real chore to update all Front ends in existence every time you make a change.
- Because the Front Ends contain the full file path name to the backend on the server, changing the location of the backend will cause the application to break.
Fortunately, Microsoft Access has a very dedicated group of users who have provided comprehensive solutions to these problems, and made them freely available to all.
Eric Rosko has written a very good utility using Access that allows developers to distribute Front Ends to all users, and have them automatically update when a ‘master’ Front End is placed on the server. This is exceptionally easy to use, and requires no VBA knowledge to implement, all you need to do is fill in a form on screen to set the paths to a few files and the updater takes care of everything else. (A Google search for ‘Eric Rosko’ should find plent of links to the correct pages)
Dev Ashish has provided a a VBA routine that can be called from a command button, which will prompt the user for the location of the backend tables should they not be the same as those held within the current front end (Click hereto see the article on this technique). Whilst this works very well, it does rely on some user interaction, so is not preferred for most applications, we would recommend creating a new Front End with the path to the MS Access backend in it, and distributing that to your users using the FE updater supplied by Eric Rosko.
Conclusion
In summary, unless you can be 100% sure that your database is never going to be used in a multi-user environment we would strongly recommend that you either build a ‘split’ application from scratch, or split your database as soon as is convenient. By using either or both of the utilities mentioned above the minor downsides of splitting can be easily overcome, leading to a far less hassled existence (and who doesn’t want that!)