One of the most important questions we see in the forums is regarding How to setup an Microsoft Access multi-user production database. So, today, I thought I’d take a stab at properly covering the subject from A to Z, and everything in between.

In the following article I will discuss

• Splitting the Database
  • Access vs Access Runtime
  • Relationships
  • File/Folder Permissions
• Securing/Password Protecting/Encrypting the Back-End
• Relinking the Front-End
• Persistent Connection between the FE and BE
• Preparing your Front-End for Distribution
• Distributing the Front-End to your Users
  • Automatic Distribution of the Front-End
  • Trusted Locations
• Other Important Things to Consider
  • Back-End Location
  • Development Environment Setup
  • Supporting Multiple Versions of Access
  • ActiveX Controls
  • Backups
  • Best Practices and Troubleshooting Steps

Splitting the Database

Right off the bat, Microsoft starts developers off on the wrong foot by creating a single database file. The only time a single file Access database should be considered, and even then, would be when you create a database for the use of a single user!  Otherwise, you MUST always split your database into two components:

• Back-End (BE) (Tables)
• Front-End (FE), the GUI if you will (queries, forms, reports, macros, VBA, …)

(You can easily split a database DATABASE TOOLS -> Access Database (under Move Data))

In the picture below, the BE would be located on the server (middle of the picture) and each laptop would have a copy of the FE for the user to use to access and work with the data.

Once you’ve split your database, then you need to place the BE in a centrally accessible location. This can be a shared folder on a PC, a folder on a server, NAS device, …

It CANNOT be one a OneDrive, DropBox, … folder over the Internet

The other thing I’d mention regarding splitting your database is you are probably best to do this in the early stages of development as some code will not work properly once split, so best to set things up right from the start so you can develop things as they will be in production and properly test things.

Before delving into the next steps required to put an Access database in Production, I think it is critical to very briefly review the overall technology setup required to deploy an Access database a little further.  Access is a file based database.  The Front-End accesses the Back-End file and all the processing is done through the Front-End.  As such, the server housing the Back-End does not require Access for you to be able to use it.  That said, every user needs to have Access or Access Runtime installed for them to be able to run the Front-End and use an Access Database.

Access vs Access Runtime

When you buy a copy of Access (at the store, through Office365, …) you are purchasing the full edition of Access.  This permits you to create and modify Access databases.  This comes at a cost, the cost of the purchasing the software.

Not all your users need to create/modify Access databases, what they most often need is to simple use an existing database.  For such users, Microsoft has a FREE Runtime Edition of Access.  This enables them to use a database, but they cannot go and change the design of Forms, Reports, VBA code, … and yes, the runtime still enables users to freely work with the data (add, modify, delete). It only limits their ability to make design changes and access VBA code.

• MS Access 2007 Runtime
• MS Access 2010 Runtime
• Ms Access 2013 Runtime
• Ms Access 2016 Runtime

So developers need to pay to get the full edition of Access, users typically only need to have the Runtime Edition.

Relationships

As with any RDMS (relational database management system), Access tables are typically setup to have relationships between one another to enforce Referential Integrity between the data held in the tables.  I thought I’d point out that, typically, the relations are created within the Back-End.

Of course, like with everything in life, there are exceptions to the rule.  These would include:

• Using multiple back-ends and requiring relations between the tables from different back-ends
• When using local tables and requiring relations between them and the Back-End tables

In such cases, then you would create the bulk of the relations within the Back-End and then create such exceptional relationships within the Front-End.

Some people will ask why not simply create them all in the Front-End and be done with it.  The simple answer is that by doing so, you leave the back-end completely unprotected.  With no relations, if someone edits or connects the Back-End directly there would be no mechanism in place to guarantee the referential integrity of the input data.  It ends up being a minimal safeguard and it also is then enforced in any applications accessing the Back-End. (and yes, no one should be directly accessing the Back-End!)

File/Folder Permissions

A critical element for proper operation of an Access database are the file/folder permissions. All users require Full Read/Write/Delete permissions on the Back-End and Front-End folders & files! This is partially because Access requires the creation/updating/deletion of a lock file (ldb, laccdb) for proper operations.

Securing/Password Protecting/Encrypting the Back-End

After finding the BE a home, this would be the time to implement a password protection and data encryption (FILE -> Info -> Encrypt with Password) on the BE, if you so choose. Do note that this can be undone at any time.  I cannot emphasize the importance of not losing this password and it is not possible to recover it should you loose it.

Relinking the Front-End

Finally, it is then time to open the FE and use the Linked Table Manager (EXTERNAL DATA -> Linked Table Manager (under Import & Link)) to relink all the table to the new BE location.

When doing so, I would urge you to not perform the relink using any mapped drives, but rather use the full UNC path.  So instead of mapping the tables to say “G:\Databases\QualityAssurance.accdb”, I would high recommend you use the Network to go and find the path so it gets mapped as “\\ServerName\C$\QualityAssurance\Databases\ QualityAssurance.accdb”.  (Quick Tip:  you can use the net use DOS command to identify the UNC paths of your mapped drives.)  Why?  Simply put, there have been several issues with mapped drive in the past and present.  There’s also the simple fact that unless your IT department is a well oiled machine and standardizes things, in many instances, different users may have different drive mappings setup, so your links, using mapped drives, would work for some and not others. By using UNC format, when relinking your tables, you avoid any such issues!

Optionally, this may be a good time to implement some form of automated table relinking code, refer to Relink Back-End Tables.

Persistent Connection between the FE and BE

One of the most important factors, for the overall performance of your database, is creating a Persistent Connection between the Front-End and the Back-End as soon as your database is launched.  Please refer to my article on the subject for all the details, see Persistent Connection in a Split Database.

Preparing your Front-End for Distribution

Now that you’ve successfully relinked your front-end to the back-end, you are finally ready to deploy your database to your end users.

But before we get to that, now is the time to seriously think about securing your front-end. Now this is a topic all to itself, so please refer to my article on the subject entitled Securing Your MS Access Database Front-End.

Distributing the Front-End to your Users

Now you simply have to issue a copy of the FE to each user to install locally on their respective PCs and they are good to go.

It is critical NEVER to share a common copy of the FE between multiple user as this highly increases your chance of corrupting the database. So placing the FE on the server where the BE is located and giving all your users a shortcut to it is a major NO-NO!!!

Automatic Distribution of the Front-End

Now, it is fine and dandy to give a copy of the FE to each user once, but this becomes a serious PITA to do each time you want to push out an update and you will need to push out updates, that’s just the way development works.

What is the solution then?

Well, there are actually many ways to approach this, and once again, this is a subject onto itself, so please refer to my article on the subject entitled Deploying Your Database Front-End to Your Users

That all said, more and more, I find a simple bat or vbs file to be the easiest and most efficient. Simply copy the master copy for the server to the local PC (folder of your choosing) and then launch it.  This way the user always has a fresh copy every time they run the application.  This also, obviously, garantees they always have the most recent version of the FE as well.  Sometimes the simplest solution is still the best!

Trusted Locations

Another very critical point about deploying an Access database to any user running Access 2007, or later, is that you must first create a Trusted Location in the registry for the database front-end otherwise the VBA code will not run and then they get warning messages about enabling the content and this often unnecessarily frightens novice users…  What’s even worse is the fact that unlike with the Full edition, Microsoft does not supply a GUI interface to set this up with  the Runtime edition.  Don’t ask me why, it just just plain dumb!

There are a couple ways to set this all up and what I privilege is to use a vbscript to distribute my FE to my users, but first it creates the necessary Trusted Location registry entry.  For more information on this see my article Create/Set Trusted Location Using VBScript.

Another very good option is to use Gunter’s AddPath.exe which can be found at http://www.accessribbon.de/en/?Trust_Center:Trusted_Locations (be sure to review the examples provided at the bottom of the page to learn the proper syntax when calling it).  Be sure to get the right AddPathxxxx.exe that matches your version of Access.  You can easily call his exe from a bat/vbscript or other method you use to launch your database.

Other Important Things to Consider

Back-End Location

Access is a network hog!  As such, it should only be run over a wired LAN connection.  No WANS.  No Wireless Networks.  No Internet Folders (DropBox, OneDrive, Google Drive, …).

Feel free to learn more on the subject by reading my article Access Back-End Location – WAN, Online Server, OneDrive, DropBox, …

Development Environment Setup

Development should always be done on local development copies of the database and never over a network connection!  You risks corruption and will, any many cases, suffer serious performance issues.  So when you want to develop your database, copy the BE and FE locally onto your PC, relink the Front-End to the local copy of the BE and do your development.  Then when everything is ready, you need to push out the new copy of the Front-End to the server and edit the server’s copy of the Back-End to synchronize it with any changes you may have made during your development.  So be sure to keep track of Back-End changes as they will need to be duplicated onto the production copy as well.

Supporting Multiple Versions of Access

When you have users on multiple versions of Access you must always do all of your development on the oldest version that will be used to run the database to ensure compatibility.

A concrete example.  Say you have users running Access 2003, 2010 and 2013, you need to do all the development using Access 2003 which would also imply using the older mdb file format (not the newer 2007+ accdb file format).

Access is Backward compatible, the Microsoft Access Dev Team has done an outstanding job of ensuring this, but it is not Forwards compatible, no software is!  So if we retake the above example, if you were to develop in 2013, there would be no way for Access 2003 or 2010 to run the 2013 file and you’d get the “Unrecognize database …” error.

Supporting multiple versions of Access is also the best time to implement Late Binding to avoid versioning issues, see: Early Binding and Late Binding.  Then again I think you should always use Late Binding!

ActiveX Controls

I’ll keep this brief, DON’T USE THEM!  They’ll only get you into trouble.

Backups

If you have done all of the above, it is now time to protect all your hard work and think Backup!  This is critical.  You can implement automated backup systems such as MS Access Auto Backup or setup a Windows Scheduled Task or get your IT Dept. to perform regular backup.  It truly doesn’t matter what approach you embrace, just be sure to set something up.  Also, be sure to validate the backup process every once an a while as I have gone into companies with backups setup just to find out they never ran, or were corrupt, …  Lastly, backup frequently!  You’ll thank me the day something goes wrong.

Best Practices and Troubleshooting Steps

You may also wish to review my article entitled Access Best Practices and Troubleshooting Steps to learn some of the best practices when creating an MS Access database.