Splitting a single Database into a Front End and Back End
All Microsoft Access Databases of any complexity should have as a minimum a separate Front End file and Back End file.
The splitting process is relatively simple. However, once split the database you need to build some mechanism for maintaining the link between the front end and back end file. This would be done by adding some forms and VBA code into the front end.
Option 1 could be performed at the same time, that is, convert to an newer version of Microsoft Office and split the database into a Front End and Back End.
Benefits of a split database
The benefits of a split database include the following:
- Improved performance - the speed of the application is usually faster
- Multiuser - the application can now be turned into a multi-user application where the data is shared across multiple front ends.
- Enhanced security - because the data is now located on a server, it can protected by the server access controls and be automatically backed up as part of the server backups.
- Improved reliability - any front end file corruption can be fixed by just getting another front end file. The back end is also less likely to be corrupted as it is sitting on a server machine.
- Extensibility - because the data is now separated from the front end either could be upsized independently of the other. For example, the data could be moved to SQL Server and the front end could remain a Microsoft Access Database.