How and why you should split an Access database

Why you should split an Access Database when sharing with multiple users, particularly when running on a Remote Desktop Server (Terminal Server)

Microsoft Access databases have a very useful facility which allows you to split it them two.   While this may sound drastic, architecturally it is really important as it can dramatically improve performance, but more crucially it reduces the risk of corruption and saves money.

When you split an Access database you create two files, a back-end database file with a suffix of .accdb which just contains the tables.   Then a front-end file which contains everything else, the queries, forms, reports, macros and modules can be distributed to users.

So, why is this so useful?   Well, here’s a list of the key benefits of splitting an Access database:

Licence Costs – If your users are only ever using the front end file saved as an executable (.accde) then they don’t need to have Microsoft Access installed.   They only need the free Access Runtime environment.   This can be a significant saving in licence costs.

Performance – Particularly when connecting to a database on a network, when users run their own front end installed on their PC the only thing travelling over the network is the data in the tables,   you don’t need to send all the queries, forms, reports etc. over the network.   On a remote desktop server this may be less crucial as both files can reside on the same machine, but because each user is only sending database edits any changes are conducted more quickly reducing any record locking time significantly.

Supporting Multiple Users – Primarily the split database architecture allows multiple users to connect to the same back end database without locking it exclusively.

Improved Reliability – Splitting the Access database reduces the risk of database corruption, the design minimises the chance of code corruption impacting data.  If a user encounters a problem and Access crashes the impact is usually limited to the individual user and doesn’t affect the underlying database which will continue to remain available to other users.

Better Security – As users don’t have direct access to the tables there is less risk of users accidentally deleting whole tables or stealing data wholesale.   On a remote desktop security permissions can be set to hide the database files themselves from the individual users or intruders who may only be able to gain access to the front end files.

If you save the front end files as an executable only file (.accde) you can restrict changes to the application by adventurous users.  The .accde file is a pure binary file and users cannot change any of the objects within it.

Scalability – Access has a limitation of 2GB for the database files.  By splitting the database you can use several back end files, each with different sets of tables, to get around this limitation.   If the data or application grows to a point where Access is no longer suitable the data can be moved to a SQL Server database while still using Access for the front end.

Running a split Access database on a remote desktop server

While there are possibly a number of ways you can structure the database files on a remote desktop server the following approach has proved stable and reliable on the Your Office Anywhere hosted remote desktop platform where each customer has their own dedicated Windows Remote Desktop Server.

A typical structure will look something like this shown below, in this example there are two users called Demo001 and Demo002, each have their own individual front end file.   In this example the files are also in their own discrete folders.  While not always necessary this method may be useful for security as you can restrict users’ access to only their own front end folder.

How do you split an Access database?

Splitting a database is generally very simple although for larger or more complex databases we would suggest talking with the original developer beforehand.   This is a fairly high level guide and there are a lot of resources on the Internet discussing this subject in detail.   There may also be specific nuances for previous versions of Access.

This guide is also predominantly aimed at customers on Your Office Anywhere’s remote desktop server so the locations of files and folders may be different than might be required in other environments.

The most important first step is to make a backup copy of the database file, and only work on the copy.  That way the original is untouched and the process is risk free.    This approach will also enable you to test the process while the users continue to use the original.  Of course if there are any changes to data in the original you will need to repeat the process after testing for a final cutover.

It may also save time to create the required folder structure if you are doing this on a remote desktop server.

Before we get started, if you want to find out more about what we can offer to help you can visit our microsoft access online page or try a free demo.

Split an Access Database using the Database Splitter Wizard

The first step is to open Access in Development mode by holding down the shift key when opening the database you copied earlier.   This bypasses any startup options or forms from loading.

Then select the Database Tools menu

Then, in the Move Data section of the ribbon click the icon cunningly titled Access Database

Click Split Database on the wizard window.

Select a folder to place the backend database.   We pre-created our folders but you can use the New Folder button if you prefer to create the folders on the fly.

The wizard puts a “_be” suffix on the file name and you may later want to continue this naming convention for the front

You should now be rewarded with a “Success” message

If you close Access now you will be left with the backend database in the selected folder and then the front end will be in the original starting folder. In theory you can use this front end as it is for each user However, to take advantage of the free Access Runtime licence you need to next save this as an ACCDE file which compiles the file into a binary executable file which also prevents users making any changes.

Open the front end file again using the shift key option

Then click File – Save As

Then choose the Make ACCDE option and then click the Save As button

Then save this .accde file.

If doing this on a Your Office Anywhere remote desktop server you can save this in one of the user’s front end folders created earlier.   You can then simply copy this to the individual folders for each user.  It may also be prudent to give each file a unique name to help with admin later.

The Your Office Anywhere helpdesk can then “publish” each of the front end files to each user so that the application is available as a RemoteApp or WebApp

Manually splitting an Access Database

Splitting a database manually is possible and may be a requirement for certain applications with a more complex architecture so is generally something for advanced users.   It is basically a process of creating two copies of the database.  Deleting all the tables from one copy, deleting all the non-table objects from the other copy then using the Linked-table manager to link the two.

Running Access on a Remote Desktop

If you’re not already a Your Office Anywhere customer and would like to find out more then visit our microsoft access online page or contact our technical sales team via the contact page.

Why Not Get A Free Trial Demo?