A while back, I discussed password protecting a database:

Access - Encryption and Password Protection

I thought I’d briefly cover the subject of Access database encryption and password protection. Historically, when we talk about the mdb file format, password protection was pretty much useless and could easily be cracked with free and widely available online tools. With the newer accdb file format things changed and cracking them seems harder. One word of caution here is that I’ve just recently been made aware that a company is developing, or has developed, an Access password cracker that they say can do so nearly instantaneously. That said, it is my personal opinion that every database should be password…

Continue reading →

and in that article I mentioned:

Today, I thought I’d explain how this can be done.
 

Securing a database requires a multi-pronged approach.  Because Microsoft is a flat file and can easily be copied and taken off-site, it can become important to consider adding measure to lock it down in a manner so it will only run in a specified directory.  This way, even if it is taken, the database simply won’t run.

The great news here is the fact that setting up such a security feature is relatively easy and only requires a few lines of code!
 

Hard Coded Folder Path

We can simply compare the current database path against the designated path and if they don’t match, then close the database. To do so, we could use a procedure such as:

Public Function IsRunningInDesignatedFolder() As Boolean
    Const sDesignatedFolder = "C:\Databases\CARDA\Demos\Security"

    If CurrentProject.Path <> sDesignatedFolder Then
        'MsgBox "This database must be run from the designated folder.", vbCritical Or vbOKOnly
        Application.Quit
    End If
    
    IsRunningInDesignatedFolder = True
End Function

A More Dynamic Approach

In some instances, we might not want to employ a hard-coded path. Perhaps, we wish to use a dynamic path, say the user’s Documents folder, AppData, … In such cases, we need only make a small modification to the original code, something similar to:

User’s Document Folder

Public Function IsRunningFromDocuments() As Boolean
    Dim sDesignatedFolder As String
    
    sDesignatedFolder = CreateObject("WScript.Shell").ExpandEnvironmentStrings("%UserProfile%\Documents\InventoryDb")
    If CurrentProject.Path <> sDesignatedFolder Then
        'MsgBox "This database must be run from the designated folder.", vbCritical Or vbOKOnly
        Application.Quit
    End If
    
    IsRunningInDesignatedFolder = True
End Function

User’s Local App Data Folder

Public Function IsRunningFromLocalAppData() As Boolean
    Dim sDesignatedFolder As String
    
    sDesignatedFolder = CreateObject("WScript.Shell").ExpandEnvironmentStrings("%LocalAppData%\InventoryDb")
    If CurrentProject.Path <> sDesignatedFolder Then
        'MsgBox "This database must be run from the designated folder.", vbCritical Or vbOKOnly
        Application.Quit
    End If
    
    IsRunningInDesignatedFolder = True
End Function

Running the Code As Part of the Database StartUp

Now that we have a procedure the perform the validation, we need to get it to run automatically when the database is launched, to do so we could employ a couple of different solutions including:

• Calling it as part of the AutoExec routine
• Making it run on opening of the database’s ‘Display Form’

Below of an template of the general approach I take for my AutoExec macro.  I use my AutoExec macro to RunCode of a Public Function ‘StartUp’ which I place in a separate module.  The StartUp function looks a little like:

Public Function StartUp()
    'Check User is allowed
    
    'Check Designated Folder
    Call IsRunningInDesignatedFolder

    'Compact Db

    'Backup Db
    
    'Relink Tables
    
    'Launch Login or Menu
End Function

As you can see, performing the folder check would be one of the first things I would do as soon as the code ran.

By implementing this type of measure, you can effectively lock your Access database to run only from a specific folder, further enhancing security and control over your application’s environment.
 

Inversly! Prohibiting The Database From Running From Within A Specific Directory

Until now, the code has been to ensure that a database only runs from a specific folder, but what about if we want the inverse? What if, say, we wanted to ensure the master copy of the server isn’t run! So we don’t want our users sharing the common master copy from the server.

Easy! All we need to do is inverse the check. Let me demonstrate.

Public Function IsRunningInDesignatedFolder() As Boolean
    Const sDesignatedFolder = "\\Server1\Finance"

    If CurrentProject.Path = sDesignatedFolder Then
        'MsgBox "This database cannot be run from within the current folder.", vbCritical Or vbOKOnly
        Application.Quit
    End If
    
    IsRunningInDesignatedFolder = True
End Function

By switching the validation from <> to =, we now will close the database if anyone tries to run it from the specified folder. This is actually a really good technique to implement!!! So they can run a copy wherever they would like, just not use/share the main server copy.