Access Database Best Practices

A common question raised by many Access users creating a multi-user database is how best to deliver your solution so that it is secure, fast, and upgradeable.  Access database deployment within your organization, or to your end customers, should be well thought out in advance to avoid performance issues, reliability problems, or potentially significant security or data loss issues.  This article discusses key best practices to deploy your Access database.

Splitting Your Database

This item is a must to make upgrades to your end users easier.  A split database in MS Access simply means you will have one file that contains your tables only.  This is sometimes called a "back end" or "data file."  For simplicity, we'll refer to it as your Access Data File.  The second file is what is usually called your "front end" database.  There is really no data stored in the file, it only contains your forms, queries, reports, macros, VB modules, and links to your Access Data File.  The advantages are:

Store your data file anywhere

With your data separated from the user interface (forms/queries/reports) and business logic (VB/macros), you can keep the data anywhere that your users are able to gain access.  Most commonly, on a shared network drive or even a shared folder on your own machine (as long as your machine is on when the file is needed by other users!).  You can back up your Access Data File whenever you need to, and relocate the file if needed.  You will need to make sure the front-end database file(s) always know where the Access Data File resides.  If it moves, you can manually restore the links to the new location, do so programmatically, or use a tool like UI Builder for Access to help prompt the user to find the new location.

Upgrade Your Front-End Databases without Data Loss

Storing your forms/reports/macros/VB in the same file with your tables also presents a problem when you need to make updates.  Particularly if you plan to deliver your database to users at a different location where you don't have direct access to the database file.  Sending users a new version won't work, as the data would be overwritten, or you would have to ask the users to import their existing data tables.  With a split Access database configuration, you simply send them a new version of your front-end file and their data remains intact.

Avoid Multi-User Collisions

If you store all your objects in one file, including your tables, Access will usually have difficulty when multiple users attempt to open the same database file.  While Access does have record-level locking, you may still receive errors that another user is currently in the database and you cannot make changes.  To avoid this, you provide each end user with their own front-end database file, each linked to the same Access Data File.

Splitting your database is quite easy with the built-in Access tool. Be sure to make a copy of your database before you begin just to be safe. In Access 2000/2003, select "Tools>>Database Tools..." from the menu, then "Database Splitter..." In Access 2007/2010, select the Database Tools ribbon tab, then "Move Data" and then "Access."  The wizard will take you through the process of selecting the tables to move, and what the your data file will be named.  It may seem trivial, but we recommend adding "_datafile" to the end of the name so you are sure you know this is the data file, not a front-end your users can delete.
 

Front-End Preparation

When preparing your front-end database file for delivery to users, you will want to consider what you want to allow your users to be able to do with your front-end file.  If you want to allow them to make design changes, open tables directly, edit or view VB source code, then you can deliver the file "as is."  More commonly, you will expect users to have much more limited capability to simply view the forms and reports as you have prepare them, without being able to view or change the form/report/code design.  The easiest way to do so is to compile Access into an MDE or ACCDE file format.  A standard Access database file is in a "semi-compiled" state which means Access does some level of compilation of any VB code you have created on the fly.  Compiling your Access database simply means that a new file is created with the extension ending in "e" instead of "b," and the objects are fully compiled such that a user cannot open a form or report's design view, nor can they inspect your VB code.  This protects your intellectual property, prevents many types of tampering, and also makes your database load (only slightly) faster.  A few other points to help with front-end preparation:

Navigation and Forms

Make sure your database includes clear navigation, and we recommend you also use our 10 Tips for a More Dynamic Access Form to add polish and make sure your users are more productive.

Remove Unecessary Menus and Options

In most cases, you'll want to hide the Access database window (2000/2003) or Access Navigation Pane (2007/2010) and provide your own Access menus.  To hide the database window in 2000 and 2003, select "Tools>>Startup..." from the menu, and uncheck the "Display Database Window" option.  In Access 2007 and 2010, select the Office icon, then the Access Options button.  In the dialog, select "Current Database" and uncheck the "Display Navigation Pane" option.  Lastly, all versions allow you to disable Full Menus (the ribbon in Access 2007/2010) and right-click Shortcut Menus if you want to further limit user's options.

Security Measures

Compiling your front-end into an ACCDE or MDE format helps protect some of your intellectual property, but if you are planning to store sensitive information in your Access project, we recommend you implement the necessary security measures to be sure your data is protected to the extent needed.  In some cases, that may simply be hiding the navigation pane to minimize the possibility that a user can find your tables without Access know-how.  In more extreme cases, it may mean password protecting and/or encrypting the front-end and data file. 

Access Workgroup Security

Because the Access 2007 file format does not support Workgroup Security, we recommend staying away from this approach since it will limit your ability to move to the 2007 format when it becomes advantageous or even required by future versions of Access. 

Table Security

Ultimately, a determined user will likely be able to get to the data if they have enough Access know-how.  A linked table in your front-end is technically a "trusted connection" where if the user can open the database window or navigation pane, they will be able to view data in the linked tables even if the data file is password protected.  If you hide the database window/navigation pane, disable full menus, and also disable the "Access Special Keys" option, you have made a large step to preventing a user from being able to get to the tables.

Navigation Security

Even after you secure your database tables, you may decide you want to limit what information some users view in your forms and reports, and what forms and reports they can even launch.  The Enterprise Edition of UI Builder offers the ability to create user-level menus in Access using the user's windows login or a custom menu prompt, or you can create your own custom menus and routines to log users in and present different options to them.  At a form level, you may want to consider whether you will obscure certain information based on the user, such as National ID or Tax ID.

Data File Locations and Performance

Your Access Data file (back end) should be located on a network folder where all users can open files in the directory.  You can also use this mechanism to limit who can access your database.  For example, you may set up your file server folder permissions to only allow the 10 users you want to be able to open your database.  If you are delivering a solution to your client, you may not have control over where the data file resides, but you can provide guidance on setting up network folder security, and making sure the folder is included in nightly backups.

When deciding where to store the Data File, it is also important to consider the file server age, hardware, and network latency.  The last thing you want is a client or user complaining that your application is painfully slow.  In some cases, it may just be a slow server.  We also recommend creating a persistent connection to a table in the Data File for each front-end when it is open.  Simply put, Access will attempt to lock and unlock the Data File each time you close a form and open another.  By creating a persistent connection, Access keeps the Data File open for the entire time the front-end is open.  To do this, you can either launch a hidden form at startup that uses a table with a few records in the Data File, or open a recordset to a table in the Data File, and keep it open until the front-end is closed.  If you are using UI Builder, this is handled for you when UI Builder starts up.  UI Builder creates a connection to the event log table (tblEventLog) that remains open until UI Builder closes.

If your solution will be used by a single end user, and you store their Data File on their machine, keep in mind that it will be easiest to have a consistent directory to store the Data File on your machine (for development) and the end user's (for production).  If you both use the same path on your local PCs, you won't need to remap the linked tables when your end user installs your front end database file.

Front-End Installation

The front-end database files should be installed on each individual's PC.  It will load faster, and you minimize the risk that two users open the same front-end on a shared network folder (causing the Access multi-user error messages we covered previously).  Importantly, your end users will not need the full version of Access unless you are allowing them to make design changes to your forms/reports/code.  You can save a great deal in MS Access licenses by leveraging the free Access Runtime edition.  When you install the front-end, you can use the free Access 2007 developer extensions from Microsoft to create an installation file, or us a free installer like Advanced Installer (what we use and love).  Make sure your main front-end form or an easy-to-access form has the version clearly labeled.  That way if you need to troubleshoot, you or the user can quickly tell what version they are running.

It's also important to note that the Program Files directory in Windows Vista and Windows 7 is no longer considered a "read/write" directory for most purposes.  Consequently, you'll want to install the front-end file in one of the new and accepted places to installed read/write programs, such as:

• The user's Documents folder
• C:\Users\<<USERNAME>>\AppData\Roaming\<<YOURFOLDER>>\

Upgrading Your Front-End

When the time comes to upgrade your front-end file with new changes, all you need to do is release an update to the end users with the front-end file only.  They simply need to run your provided installation file, or replace their current copy of the ACCDE/MDE front end file you initially provided.

Changing Your Data File

If you have direct access to the Data File, making changes to the tables is usually easy.  Your biggest task will to be to make sure all users have exited their front-end databases so that you have full read/write access to the table design.

If you are delivering a packaged solution to customers, changing their Data File can be a bit more involved.  A few approaches to consider:

Future Use Fields

We use these quite a bit in our products. They aren't fool proof, but they can help you minimize table changes when you want to add a few new features that require more fields in an existing tables.  Essentially, you create a set of fields in each table named "Future-Use-1" "Future-Use-2" and so on.  You may want to create 4-5 that are Text(255), several more that are Double numeric fields, and a few Date fields.  When you decide you need a new text field to store something you didn't know your customers/users wanted to track, you've got a field available to do so.  The form will have a caption like "Customer Segment" while in the table (which your users won't see) you are making use of "Future-Use-1."  Avoid adding too many future use fields, as it can (minimally) increase the space each row in the table will consume on disk.  But if you have 200,000 records, an extra 2kb per record can indeed make a difference!

Upgrade Code

Another option is to have a flag set in the front-end file to run upgrade code when you deliver a new version of your front-end file to users. When your database opens, if the flag equals true, your application attempts to open up the Data File (using DAO or ADO) and make the changes to the table(s).  After it is done, it sets the flag to false.  This does require solid experience with ADO or DAO, and we won't post any code here.  But it is entirely feasible to do.