By now, we all know that the proper setup of an Access database is to split the database into a back-end (tables) and front-end (everything else) and then deploy the back-end on a central share and an individual copy of the front-end to each user’s local PC.  Furthermore, both the back-end and front-end required Read/Write/Delete permissions.

If you’re not already aware of this, I’d recommend you review:

Why Do We Actually Need Read/Write/Delete permissions?

It is because of Microsoft Access’s lock file.  When you launch an Access database, it will automatically create a lock file (ldb, laccdb) and write to it.  When you are done with the database and close it, it will then attempt to delete the file (assuming you are the last person using the file).

Thus, Access requires Read/Write and Delete permission on each of the folders.

So how can we validate the permissions as users and developers?

Manual Validation Of File/Folder Permissions

Anyone can easily validate the permissions by simply:

For both the front-end and back-end directories:

1. Creating a new text file in it
2. Opening the text file, adding some text and saving the changes
3. Confirming the changes were saved by reopening the file
4. Deleting the file

If any of these steps fail, then the folder permissions are insufficient to run an Access database properly.
 

Using VBA Automation To Perform The Validations Of File/Folder Permissions

I was originally going to use code to try and read the folder security permissions and then thought to myself, why not simply replicate the manual approach as it is so simple and effective.

The heart of the process is a simple function:

Public Function CheckFolder(ByVal sFldr As String) As Scripting.Dictionary
    On Error GoTo Error_Handler
    #If FSO_EarlyBind = True Then
        'Early Binding Requires a Reference to: Microsoft Scripting Runtime
        Dim oFile             As Scripting.File
        Dim oResults          As Scripting.Dictionary

        Set oResults = New Scripting.Dictionary
    #Else
        Dim oFile             As Object
        Dim oResults          As Object

        Set oResults = CreateObject("Scripting.Dictionary")
    #End If
    Dim bResult               As Variant

    sFldr = TrailingSlash(sFldr)

    'Does it exist!
    bResult = FSO_Folder_Exist(sFldr)
    oResults.Add "Folder Exists", bResult
    If bResult = False Then
        oResults.Add "Create", "Unknown"
        oResults.Add "Modify", "Unknown"
        oResults.Add "Delete", "Unknown"
    Else

        'Can we create a file
        bResult = CBool(Nz(FSO_File_CreateFile(sFldr & "PermissionCheck.txt", "Line 1", True), False))
        oResults.Add "Create", bResult
        If bResult = False Then
            oResults.Add "Modify", "Unknown"
            oResults.Add "Delete", "Unknown"
        Else
            'Can we write to the file
            bResult = CBool(Nz(FSO_File_AppendFile(sFldr & "PermissionCheck.txt", vbCr & "Line 2", TristateFalse), False))
            oResults.Add "Modify", bResult

            'Can we delete the file
            bResult = FSO_File_Delete(sFldr & "PermissionCheck.txt")
            oResults.Add "Delete", bResult
        End If

    End If

    Set CheckFolder = oResults

Error_Handler_Exit:
    On Error Resume Next
    If Not oResults Is Nothing Then Set oResults = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: CheckFolder" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

and then we can simply use it to check any folder we wish, for instance:

Sub Check_FrontEndFolder()
    Dim oResults              As Object
    Dim vKey                  As Variant
    Dim sFldr                 As String

    sFldr = Application.CurrentProject.Path
    Set oResults = CheckFolder(sFldr)

    Debug.Print "Permissions Check On: " & sFldr
    For Each vKey In oResults.Keys
        Debug.Print , vKey, oResults(vKey)
    Next

    Set oResults = Nothing
End Sub

which will in turn output something like:

Permissions Check On: C:\Databases\Folder Permissions
              Folder Exists True
              Create        True
              Modify        True
              Delete        True

Or if we test a folder that doesn’t exist:

Sub Test_BogusFolder()
    Dim oResults              As Object
    Dim vKey                  As Variant
    Dim sFldr                 As String

    sFldr = "C:\Temp\NonexistentFolder"
    Set oResults = CheckFolder(sFldr)

    Debug.Print "Permissions Check On: " & sFldr
    For Each vKey In oResults.Keys
        Debug.Print , vKey, oResults(vKey)
    Next

    Set oResults = Nothing
End Sub

well then it returns:

Permissions Check On: C:\Temp\NonexistentFolder
              Folder Exists False
              Create        Unknown
              Modify        Unknown
              Delete        Unknown

So on and so forth.

So with this is now possible, at the startup of your db, to validate the necessary permissions are in place, and otherwise pop-up a message to advise you users to contact their database administrator for assistance.

You can also find a opy of my user defined Nz() function for those application that don’t have it natively (Excel, Word, Outlook, …) in my article: An Nz() Function For All Applications.