How to Encrypt a Split Database in Access 2016
You can encrypt a split database by encrypting the backend database, unlinking the tables from the frontend, then re-establishing the link.
Encrypting a database makes the data unreadable by other tools, and it sets a password that is required to use the database.
Encrypting a split database requires a few extra steps when compared to encrypting a non-split database.
Here's how to encrypt a split database.
Encrypt the Backend
First, you must encrypt the backend/source database. Then you need to reestablish the linked tables in the frontend database.
Here's how to encrypt the backend.
-
Open the Backend Database in Exclusive Mode
The source database must be opened in exclusive mode before you can encrypt it and set a password.
To open the database in exclusive mode, select Open Exclusive when opening the database via the Open dialog.
For more complete instructions, see How to Open a Database in Exclusive Mode.
-
Open the File Menu
Click File in the top left of Access to open the File menu.
-
Select the Encryption Option
Click the Encrypt with Password button (from the Info menu).
-
Set a Password
Enter your desired password.
Enter it again to confirm.
Click OK.
-
Acknowledge Row Level Locking Message
If you get a message informing you that Row Level Locking will be ignored, click OK.
The database is now encrypted.
About The "Row Level Locking" Message
A block cipher is a method of encrypting text in which a cryptographic key and algorithm are applied to a whole block of data at once, rather than to each bit at a time.
Row level locking (or record level locking) is where a record is locked whenever it is updated. This is to prevent other users from accessing data being while it is being updated.
When you update a record in an encrypted database that uses row level locking, only the record (row) is encrypted, not the whole database.
When you encrypt a database with row level locking disabled, you will receive the Encrypting with block cipher is incompatible with row level locking. Row level locking will be ignored. message.
What this means is that the whole database will be encrypted — not just the record.
Update the Frontend
Once you've encrypted the backend database, you now need to unlink, then relink the tables that point to the backend.
Unlink the Tables
Here's how to unlink the linked tables.
-
Open the Frontend Database
Open the frontend database.
Click OK if you a Not a valid password message.
-
Acknowledge any other Messages
You may receive other messages, depending on the design of your database. For example, if you have an AutoExec macro (i.e. one that runs as soon as the database is opened), you may receive the message in the screenshot.
This is to be expected, because the macro uses data from the backend database, which has just been password protected. Seeing as we haven't provided the password at the frontend yet, the macro can no longer access the data in the linked tables.
In this case, we can simply click Stop All Macros.
-
Unlink the Tables
To unlink the linked tables, right-click all tables to be unlinked, and select Delete.
This will remove the tables from the frontend, but they will still remain in the backend.
-
Confirm
Click Yes to confirm.
Relink the Tables
Now we can relink those tables (and provide the password when we do so).
-
Launch the Import/Link Wizard
Click Access in the Import & Link group from the External Data tab on the Ribbon.
-
Enter the Backend Database Details
Enter the location of the source database (or browse to it using the Browse button).
Select Link to the data source by creating a linked table, then click OK.
-
Enter the Password
Enter the password and click OK.
-
Select the Tables
Select all the tables that need to be linked, then click OK.
-
Finished
The split database is now encrypted and password protected.
You can now open a table and view its data.
Also, any AutoExec macros will run automatically as soon as the database is open.