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 links.

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.