In its simplest form, an Access Application, or indeed any database application is made up of two components, Data and User Interface (UI).
This is referred to as a Client Server architecture where the Data, otherwise known as the Backend (BE), is situated on a Server where it can be shared by all users (although not accessed directly) and the UI (Client), a copy of which lives in each user’s workspace whether it be a physical or virtual machine. The UI file is often referred to as the Front End (FE).
Why Split?
Although Access will allow you to open a file in either Exclusive or Shared mode, sharing an Access File will result in corruptions, slowdowns and locking conflicts.
The other main reason for using a Client Server architecture is that the UI can be replaced in a version upgrade without affecting the data.
This is not just ‘Best Practice’, it is Standard Practice.
Application File (FE)
The application file contains all of the forms and reports that the user interacts with forming the UI (User Interface).
During an install or upgrade the FE is copied to each user’s computer. This FE file links to the tables stored in the common Data File on the Server so that all FEs share the same data.
Data File (BE)
Your data is not kept in the FE file. Instead it is kept in a separate database that is shared by all user’s FEs. Usually this BE is an Access Database file but could also be a SQL Server Database.
The Data File is located on a server. Users should not have direct access to this file but must have permissions to allow changes to occur to the Data File via the FE.
The server does not need to run server software, it can be a regular PC on a Pier to Pier network but it is best that this machine does not have anyone working on it directly as this will slow down data delivery to other users. When you use an Access File as your BE, rather than a SQL Server Database, the server can be a NAS.
Your BE should be backed up on a regular basis. Although a backup of this file is usually done as part of the normal server backup, the Backup Process for an Access BE is an additional process that should be done often.
This makes a copy of the BE and Compacts it making it more efficient and less disposed to corruptions. Each time this Backup Process is run a copy of the BE is made with a name that includes its date and time. For example My_Data_150131.accdb
All other users need to be logged out of the application to run a backup. You will find a link to the backup process on the ribbon associated with your main menu.
With a copy of the application on each user’s computer, file sharing issues are avoided and network traffic reduced to data only.
This also allows for distribution of new versions without affecting the data file.
The Installation is then a two part process
Install the Data File
When first installing the application, the data file is manually created or copied to the server.
Installing the Application File(s)
The application file is copied to each work station. This can be done automatically by an install package or manually.
For most of my applications, an install package is copied to the server. it is then just a matter of running this installer on each work station.
Microsoft Access
If a computer does not have MS Access installed then the free Access Runtime can be installed to allow the application to run.
I normally include the Access Runtime as part of the Install Package. This ensures that all users are running the same version and the added benefit of using the Runtime is that users cannot change the design of the application.