DataWright Information Services

Consulting and Resources for Excel and Access

Re-linking to a password-protected database

If you create a multi-user database you should split it into a
front end (with all the business logic) and a back end containing
the tables. The back end belongs on the network in a shared
directory; the front end should be on each user’s local PC. Of
course, putting files on the network can expose data to people who
should not have access to it. Part of database protection is
limiting who can view your data, and this can be done using two
simple steps:

Place the back end database in a network directory

Your IT department can provide you with a directory whose access
is limited to those who will be using the database. The directory
must be read-write for all users who can view it.

Test read-write by navigating to the directory and trying to
create a text file. If you can’t, you do not have write permissions
in the directory and will be unable to open an Access database
placed there. Get IT to update your permissions.

Password-protect the backend database

If you want to only have users viewing your data through the
front end database you created, you can password-protect the back
end database to stop people from directly opening it.

  • Launch Access.
  • File > open, browse to the back end database, and click the
    drop-down arrow next to the Open button. Pick Exclusive, and the
    database will open.
  • Set the password. For 2007 and higher, Database Tools > Set
    Database password; for 2003 and earlier, Tools > Security > Set
    Database Password
  • Close the database

Rebuild the links to the data

Now open the front end database again. When you try to launch a
form, table or query you will get an error because the database
cannot link to the back end any more. You will find that the Linked
Table manager doesn’t help at this point; you need to re-create the

For Access 2003 and earlier

  • Browse to the Tables view and delete every table link (only
    the ones with the arrows; not the local tables)
  • File > Get External Data > Link Tables, open the backend
    database and enter the password when prompted. Select all
    tables, press OK, and you should be up and running.

For Access 2007 and later

  • In the Navigation pane display all tables. If you have a
    combination of local and linked tables the local ones will be
    grouped first, with the linked tables at the bottom of the list.
    Delete all of the links (the ones with the arrowhead in front of
    the icon).
  • Data > Get External Data, open the backend database and
    enter the password when prompted. Select all tables, press OK,
    and you should be up and running.