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.
