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
(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
(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.
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.
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
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
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.
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.