Securing a Microsoft Access database with separate front-end and back-end

This page applies to Access versions 97 to at least 2003.

People with questions about securing an MS-Access database are generally referred to the Microsoft Access Security FAQ.

This does a pretty thorough job of explaining how you secure a single mdb file. This knowledge is however insufficient, given that it is strongly recommended – indeed virtually essential – to split every Access database into two separate files. (If you are unsure why, I give notes on the reasons at the bottom of the page.)

This page fills in the missing information, and emphasises one or two points which don't in my view get sufficient exposure in the FAQ. I have put it together on the basis of several discussions in the Access newsgroups and my own experience.

Limits to security

One thing that it is important to understand before you start (but which in the FAQ is buried under a rather misleading title somewhere around page 9) is that it is not possible to make an Access database (or indeed any file-system-based database) very secure. You can secure it against people with no technical skills and against casual hackers. If the information in the database is only moderately sensitive, then this is probably sufficient.

But if security is really important then you need a server-based database, such as Oracle, SQL server or PostgreSQL.

Steps

The steps for setting up the secure database are as follows.

  1. Firstly, place your back-end in the appropriate network directory.
  2. Adjust the table links in the front-end to point to your back-end tables.
  3. Make a backup copy of the files, in case something goes wrong while setting up the security.
  4. Secure the back-end, following the instructions in the Microsoft Access Security FAQ.
  5. One can secure the front-end as well. However in nearly every case it is the security of the data which really matters. The integrity of the front-end can most readily be achieved by distributing it as an MDE file. This removes all editable code from the database and makes the design of forms, reports, and modules inaccessible to users. Menu-selection: Tools / Database Utilities / Make MDE File.
  6. Decide where to place the front-end on each user desktop. Based on this, create a shortcut with the following syntax (the quotation marks are necessary if spaces occur in the path or file names, otherwise they are optional):
    "Access executable" "front-end file" /wrkgrp "workgroup file"

    An example:
    "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\ABCapplication\ABC.mde" /wrkgrp "\\fileserv1\ABC\security.mdw"

    It is easiest to construct the shortcut in a text-editor and paste it into the shortcut properties. Note that there should not be any line terminators, although the above example wraps. Note also that it is recommended to include the share name (e.g. \\fileserv1\) rather than the drive name (e.g. N:) in the network reference, to protect yourself against configuration differences.

    Note that if differing configurations of the user desktops mean that it is not possible for every user to use the same shortcut syntax, then it gets a bit messy. Possible solutions, none of them really good, are:

    1. distribute different shortcuts for each configuration;
    2. give users detailed instructions for creating their own shortcuts;
    3. install shortcuts yourself on all the user desktops, or at least all those which are non-standard.
  7. Distribute the front-end and shortcut to the users, telling them in which directory they have to place the front-end. (Obviously if you have an automated software-distribution system available, this is more straightforward.)

Splitting the database

As mentioned above it is important to split an Access database into a back-end containing the tables and a front-end containing everything else – forms, reports etc. This is for two reasons:

  1. maintaining the forms and reports in a sane fashion requires a separate front-end;
  2. having them combined will almost certainly result in Access hanging and probably file corruptions (unless you never have more than one concurrent user).

The only time it isn't necessary to split a database is when it is for your sole personal use.

In addition it is important that a separate copy of the front-end is distributed to every user to run on their own computer. This is because having everyone run a single copy in a network directory is likely to result in:

  1. excessive network traffic;
  2. slow response to the user;
  3. possibly file corruptions, though these occur less frequently than if you have the forms and tables in a single file.

You might get away with a single front-end copy for a while, especially if you have a simple database with few concurrent users. Then again, you might not.