Protecting Queries , Macros And Table Links in MS Access 2016

Introduction

Many people have developed their applications in Microsoft Access, but there security concerns that need to be addressed before we pass our applications to the users. Below are issues which we have to take care of:

(1) Login forms

(2) Our treasured code

(3) Table links

(4) Reports

(5) Queries

(6) Macros

(7) Forms

(8) Modules

(9) Startup forms

Currently, we rely on the Accde/Mde format to protect some of the above objects, I’m using a term some, why? Because the listed objects below are left as orphans, with only the almighty to protect them:

(1) Table links

(2) Queries

(3) Macros

(4) Startup forms (But here to some extent)

Now, what do we do to provide extra protection for the orphans as well? We have to borrow the concept of a padlock. Those who are living in developing countries will be very familiar with padlocks. People use padlocks to protect their merchandise from thieves when they close their business on each busy day, and so we have to create this padlock from scratch, not a physical device, but as a software type.

Step 1

The starting point here is to find ways of disabling the Start-up properties:

Using VBA to Lock Your Application Down

To use my example, use a blank database with no data in it. Insert a module into the VBE window using Insert Module from the menu.

Enter the following code into the module: (Caution this code is only tested for Ms Access 2016)

Sub DisableStartupProperties()
ChangeProperty "StartupShowDBWindow", dbBoolean, False
ChangeProperty "StartupShowStatusBar", dbBoolean, False
ChangeProperty "AllowBuiltinToolbars", dbBoolean, False
ChangeProperty "AllowFullMenus", dbBoolean, False
ChangeProperty "AllowBreakIntoCode", dbBoolean, False
ChangeProperty "AllowSpecialKeys", dbBoolean, False
ChangeProperty "AllowBypassKey", dbBoolean, False
ChangeProperty "AllowShortcutMenus", dbBoolean, False
End Sub
Function ChangeProperty(strPropName As String, _
varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As Database, prp As Property
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
ChangeProperty = False
Resume Change_Bye
End If
End Function

This code comprises a function that will change the property value. If the property is not found, it will be created. When this code is run, it will set the properties shown in the code to False. This stops, for example, the BREAK key and the BYPASS key (SHIFT) being used to interfere with the database. It also inactivates shortcut menus (right-click a form or report) and prevents the database navigation window from appearing.

Step 2

Now here two unbound forms are required as follows:

(1) Form one to open special form two with keys 

Form one creates two controls as below:

(a) txtPassword (This control must be used for a password and must be masked)

(b) CmdPasswordClick(This button will be used to validate the password and hide form one as well) and lastly

(c) Put a code behind it like below:

Private Sub CmdPassword_Click()
Me.Visible = False
End Sub

The above code will hide the form in question, but not closing it because we want to use the password we have keyed it in for validation in Step 3 below.

(2) Special form two, this form must have the following controls:

(a) Lock database (Magic key) with a code attached to it as below:

Private Sub CmdMathscience_Click()
Call DisableStartupProperties
End Sub

The above code will lock your database properties, if you do not have the magic key then you are locked out permanently no more production or changes you may want to make to your database will be allowed.

(b) Unlock database (Special key) with a code attached to it as below: (This has a control called txtEagles masked as well)

Private Sub Cmdunlockdb_Click()
Dim Cancel As Integer
Dim 20s6p As String
Beep
If Me.txtEagles <> "20s6p" Then
MsgBox "You Are Trying To Challenge The Tree Of Wisdom Please Back Off Now", vbOKOnly, "Back Off Now"
Cancel = True
Me.txtEagles = ""
Me.txtEagles.Requery
Exit Sub
ElseIf Me.txtEagles = "20s6p" Then
EnableStartupProperties
MsgBox "All the special keys are now released", vbOKOnly, "Please Proceed Now"
End If
End Sub

Please notice that the password is included in the code.  The above code will help you to unlock your database normally as before

(c) Exit control button

Private Sub ExitEaglesNest_Click()
DoCmd.Close
End Sub

Step 3

Include the code below to form two to help protect this special form:

The purpose of this code is to protect your form two. When you try to open form two the form will open form one first. If you do not provide the correct password then form two will not open at all.

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmFormOne", , , , , acDialog
If Forms!frmformone!txtPassword = "h860gt" Then
DoCmd.OpenForm "frmFormTwo"
Forms!frmPass!txtPassword = ""
Else
Beep
MsgBox "Wrong Password", vbOKOnly, "CA Premier Accounting Package"
DoCmd.Close acForm, "frmformOne"
DoCmd.Close acForm, "frmformtwo"
End If
End Sub

The above padlock and its keys will help to protect your database:

(1) At login, users will not be able to use special keys

(2) No right clicks

(3) At least the average users will not manage to break it

(4) All queries, table links and macros will be protected

Step 4

Finally

Right-click the form two you generated and select Properties. Choose Form properties on the drop-down and navigate to Key Preview. Set this property to Yes and save the form. This means that keypresses on the form can be picked up by your code.

Step 5

At startup form clear the Microsoft Access ribbon and use the XML code below:

You need to design a simple ribbon that will remove the existing Access ribbon. To do this, you must first create a table called USysRibbons. You do this by clicking Create in the Access menu bar and then clicking the Table Design icon in the Tables group of the ribbon.( In a split database please note this table must be part of your Front End otherwise if you put it in the Back End you will not able to open the FE )

You need to create two fields in the tables. The first is called RibbonName and is a text field. The second is called RibbonXML and is a memo field. Save the table with the name USysRibbons.

In the database navigation window, right-click the bar at the top of the navigation pane and click Navigation Options. Check the box for Show System Objects. Because this is a system table, you will not see it or be able to find it unless you do this.Open the table and type in the value MyRibbon in the RibbonName field. Enter the following XML in the RibbonXML field for that record:

<customUI
xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
</ribbon>
</customUI>

Because this uses startup from Scratch, do not forget to switch on the error checking messages, this is very important and will help in the event when your XML code is incorrect. Once your ribbon is cleared simply apply the ribbon to your startup form. It will hide the custom ribbon and that is it. Done!

This article was written by Christopher Hankwembo based in Lusaka, Zambia, Southern Africa. Christopher is a qualified Chartered Certified Accountant and a Certified Internal Auditor.