How to Connect to External MS Access File With Password Projected

It is a very good practice to separate your MS Access application (MS Access code file - front end)  away from MS Access data (table - back end). One of the good reason is that when you need to update your code you don't need to touch any data.

To give you a better picture, when you do the coding like add new report, modify the form, delete the unwanted stuff on your code, you don't have to worry about the data on the database. The only thing you need to maintain is only your code. When it is the time to update your MS Access application you can just replace the application file and leave the data file alone. This is the very basic and a must for MS Access programming.

For security sake, when separate the front end from back end it is also a good practice to password protect the database file. The complication happen when you want to connect to this protected database file. Here is how to handle with this complication in your VBA.

Before open any connection to your database file, execute the following VBA code in your application file.

Dim path

Dim db As DAO.Database

Dim ws As DAO.Workspace

path = "c:\my_data.accdb"

Set ws = DBEngine.Workspaces(0)

Set db = ws.OpenDatabase(path, True, True, "MS Access;PWD=yourpassword")

Where c:\my_data.accdb is your MS Access database file and yourpassword is the password in the database file.