It Takes Two

Microsoft Access has split personalities.  It's both a rapid application development (RAD) environment and a file-based database system.  It is actually quite capable in each area.  But they are two fundamentally different functions.  It's important to treat them as such.  And that means splitting any Microsoft Access-based application into two pieces: a front end and a back end.

Front End - User Interface

The "front end" is the user interface.  It includes the forms, reports, and navigation with which the end user will interact.  The front end should contain no user-editable persistent data.

Let's unpack that a bit.  Persistent data is information that should be saved from one session to the next.  This is in contrast to temporary data which may be used to increase performance, but that can be thrown away at the end of the session.

The forms and reports of an Access database are susceptible to corruption, especially when there are multiple users using the same front end file simultaneously.  The best practice is to provide each user with their own copy of the front end.  

If you're not sure whether a particular table belongs in the front end or the back end, here is an easy way to think about it.  The front-end should be completely disposable.  You should be able to tell a user to delete their copy of your application's front end database and give them a brand new copy without them losing any important information.

Back End - Persistent Data

The back end is for persistent, user-editable data storage.  This includes all of the data that the user will be editing in the front end half of the application.  The back end of an Access application is often an Access database file, but it does not have to be.  Just about any combination of database systems can play the role of the "back end."

In fact, a very common and very robust option is to use a client-server database--such as Microsoft SQL Server--as the back-end data storage.  This setup gains you all of the rapid application development advantages of Access when creating forms and reports along with the enhanced security, reliability, and feature-set of a client-server database.

The downside to using a client-server database is the added complexity in setting up another piece of software.  There is also added cost for standard versions of SQL Server.  However, cost should very rarely be a consideration.  SQL Server Express, the free version, has a maximum database size of 10 GB.  That's five times higher than the 2 GB maximum for Access files.

Access will also work with open-source relational databases like MySQL and PostgreSQL.

Bottom Line

It doesn't matter too much what you use for your back end data storage.  The only truly important thing to remember is that any Microsoft Access "application" must be split into a front end and a back end.  The front end should be completely disposable and replaceable.  The back end should be regularly backed up.

As long as you follow those two guidelines, you will be in good shape.