Access Security Ideas from an Experienced Developer
Some good Access security/protection ideas from a reader who wishes to remain anonymous because he
wants his application security kept a secret.
Hi Garry, I thought it might be a good idea to share these
ideas with you about security that I thought of while reading your book.
Note: Since my main application is distributed as an mde file
I have not reviewed these features specifically for use in mdb files. There will
be some additional security considerations for mdb files.
You may want your application to behave differently on your
development machine to how it behaves on the client’s machine. To do this,
create a ‘secret’ text file, for example DevsComputer.txt, to identify that this
computer is your developer’s computer. I put it in the root directory
(C:\DevsComputer.txt) so it is easily accessible to applications in any folder.
When you need your application to behave differently, just check for the
existence of your secret developer file with the following code.
If Dir(“C:\DevsComputer.txt”)<>”” Then
You could run this procedure when the application opens and set a global
variable which you can more easily access from anywhere in the application.
Global gDevMode As Boolean
gDevMode = Dir(“C:\DevsComputer.txt”)<>””
in any procedure …
If gDevMode=True Then
I usually use this to turn on or off various environment variables such as the
AllowBypassKey, AllowSpecialKeys, AllowFullMenus, Show Hidden Objects. It is
also useful for debugging as described in the next section. And when the
application quits, I use it to have the application return to the database
window for more development, while it quits completely on the client’s machine.
If you want to look at how the application will work on the client’s machine,
just rename the text file so it can’t be found. I usually just put an x in
front, e.g. xDevsComputer.txt.
Then I can easily remove the x when I want to go back to
developer mode. If you are working on site, you can create the text file on the
client’s computer if you need to use specific developer features. But don’t
forget to delete it before you leave! (Might be a good idea to remove it from
the recycle bin too.) This process is not particularly secure if your clients
are inclined to view your code. You may need to consider the likelihood of this
happening and how it might compromise your application.
Generic error handler +/- logging.
Ref Chapter 4 in Garry’s Access Security Book
There are plenty of generic error handling procedures already described. This is
just a small addition to improve the developer’s lot. When developing an
application, I want the code to show me the exact line where an unexpected error
occurred. The code below shows you the error message, then you can choose
whether to continue with the normal error handling routine, or whether to break
into the code. If you go to the code, hit the F8 key twice to go to the line
that caused the error. The key is to use the Resume statement – but this should
never be used except in break mode or the app may go into an infinite loop.
Note that you need to use the Developer Mode function in the error handling
Private Sub RunSomeCode()
On Error GoTo ErrHandler
<Your code here >
Select Case Err.Number
Case XXX 'For a specific error, add code here to manage that error
<Your error management code for specific error>
Resume Next 'Or Resume or Resume ExitHere
If ShowGenericErrorMessage("RunSomeCode") = True Then
Resume 'To manually debug
Public Function ShowGenericErrorMessage(ProcName As String) As
'Don't add any error code to this procedure since it will reset the Err
'to 0 and prevent the error being displayed.
'Called by various error procedures
'If false the code continues in the calling procedure
'If true the code stops in debug mode in the calling procedure
'Reset the environment in case it had been modified by the calling procedure
If Dir("C:\DevsComputer.txt") <> "" Then
ShowGenericErrorMessage = MsgBox("The following unexpected
program error” & _" has occurred:" & vbCrLf & vbCrLf & "Module: " &
_ Application.CurrentObjectName & vbCrLf & "Procedure: " & ProcName & _
vbCrLf & "Error number: " & Err & vbCrLf & "Description: " & Error & _
vbCrLf & vbCrLf & "Do you want to debug the program code?", vbExclamation + _
vbYesNo + vbDefaultButton2, "Unexpected application error") = vbYes
MsgBox "The following unexpected program error has occurred:" &
vbCrLf & _
vbCrLf & "Module: " & Application.CurrentObjectName & vbCrLf
"Procedure: " & ProcName & vbCrLf & "Error number: " & Err & _
vbCrLf & "Description: " & Error & vbCrLf & vbCrLf & _
"Please inform technical support. ", vbExclamation, _
"Unexpected application error"
ShowGenericErrorMessage = False
Turn bypass key back off in code after startup.
If clients work out how to re-enable the Shift Bypass in your
code, you can have the application turn it back off in its startup procedure.
This won’t stop the persistent hacker, but will ensure the application behaves
itself most of the time. Garry discusses this in detail in Chapter 2 of his
How to licence users
Many developers use a specific licence key to unlock functions
of their applications. I have seen very few descriptions of how to do this. My
own method requires the code to be secured from prying eyes – using an mde file
is the easiest way to ensure this. This system works well with no user level
My application has 4 levels of client permission each with
different functions. These are
• Demonstration – clients can create only 25 accounts in a single back end
• Single user – clients can use the system for unlimited accounts with one back
• Multiuser – clients can use up to 5 different back ends
• Unlimited – clients can use unlimited back ends
To manage these different levels, I use a single registration
number. The registration number determines which level of access a client
receives. If the client has no registration number, the system operates in
One way to have the system recognize a registration number
would be to store a large number of possible registration numbers in the
database. However, this has several problems – where do you store them – how can
they be secured – what if you need more – etc. A better way is to have the
system use a formula to determine if a registration number is valid.
With my application, I also wanted to be able to prevent one
client giving his registration number to another client. Consequently I needed a
way to register the application to a specific client. What I settled on was a
method which involved reading the disk serial number in code, and tying the
registration number to the serial number.
Each time the application opens, it reads the disk serial
number and compares it to the registration number. If they don’t match
correctly, the system runs in demonstration mode. The registration number itself
is unsecured, the client knows what it is and can give it to others. But it
won’t work on their machine. To make this work I have a registration form in the
client’s app which displays a manipulated version of the disk serial number. The
client gives me this number and I create a registration number which will match
it. By match it I mean that the two numbers obey a specific mathematical
relationship. If the disk serial number is not the one which was matched to the
registration number (say the client tried to install on another computer) the
mathematical relationship would no longer be valid and the system would revert
to demonstration mode. The mathematical relationship and number manipulation
code is hidden within the mde so it cannot be discovered by the clients.
After being entered, the registration number is stored in the
front end application in a hidden table (not that it can’t be found). One
problem which arose was that when clients installed a new front end they would
have to reinsert their registration number. To avoid this, I save the
registration number to a text file and have the system look for it when it is
not found in the front end. Of course it checks it against the disk serial
number too to make sure it is valid.
Using the mathematical relationship, you can set up different
registration numbers to unlock different functions. So if disk number = 2 x reg
number, then function set A might be active, while if disk number x 4.357 = reg
number, the function set B may be active.
If a client reformats their hard drive or buys a new computer,
they will need a new registration number. It’s easy to automate this by setting
up and appropriate client database which the ability to enter a serial number
and have a registration number created – they email the computer ID and you
generate a new matching reg number and email it back.
USER INTERACTION PLEASE … If any of the readers of this
article would like to share their own ideas on how to register their databases,
please send them to Garry using the contacts links on the side of this page.
Winzip command line + password
Often my clients need to email me their databases for
development or troubleshooting purposes. Since these may contain sensitive
information, I feel uncomfortable that the data may be picked up in transit on
the Internet somewhere. What I prefer to do is to have the client zip their data
into a password protected Winzip file. However, this is not second nature to all
clients, and in some cases, they need to do this regularly. Consequently a bit
of automation goes a long way.
If you have the registered version of Winzip you can also get
the command line add on which allows you to create zip files from code. However,
even the standard version of Winzip accepts command line instructions although
these are not documented on the site. For example, the following line will
create a zip file from code if you have Winzip installed (even the unregistered
files\winzip\winzip32.exe -a -ex """ & strDestination & """ """ & strSource &
If you have the unregistered version, you will have to click
the I agree button in the Winzip dialog box before the file will be created.
You can add a password using the following syntax …
"c:\program files\winzip\winzip32.exe -a -ex
-s" & strPassword & " """ & strDestination & """ """ & strSource & """",
For ease of use without compromising security too much, you
and your client don’t have to use a specific password, you can have the code
generate this for you. If you settle on a particular format which includes the
current date and/or time, the password can be different each time, but you will
both be able to decipher it. For example, use ClientYYYYNameMMM!DD, where
YYYYMMMDD is the date the file was created. The recipient can then simply check
the date on the received file and work out what the correct password is. Not
foolproof, but not a bad compromise.
The string strSource can include multiple files and wildcards.
Note the use of double quotes and spaces in the code.
Note that there are plenty of zip utilities on the Internet
which you can incorporate into your applications (not everyone has Winzip). Many
of these are active X controls and vary in cost from 0 to $300+ for royalty free
Mostly I provide updates to my clients as self extracting zip
files. While these are fine for most people, I spend considerable time helping a
small number to extract and install files and even to help them find out whether
they need the update.
I have now developed a system where the clients can click a
button in their application to check my website for updates, and to download and
On the site, I put a csv file containing the latest
versions of the relevant files. Included is the version, filename to download
(zip file), file name contained in the zip, and notes.
Using the ftp/http functions from the AccessWeb (http://www.mvps.org/access/modules/mdl0037.htm),
I put a button on my application to check for updates. This downloads the csv
file to the client computer. Then the csv file is attached as a linked table,
and the versions in the file are compared with the versions in the system. If
there is a newer version listed in the csv file, the user is given the option of
downloading and installing it.
Downloading the files is done with the same code. Next, the
downloaded files must be unzipped. I had to invest in one of the Active X
components to do this as I couldn’t rely on all clients having Winzip. Using the
properties of the Active X controls, I unzip the downloaded files to their
correct folders. Mostly these files are updated back end tables which can be
copied while the front end is open (as long as no bound forms are open).
When I have to update the front end itself, the file is
unzipped to a separate folder (I create a LiveUpdate folder as a subfolder of
the data location) and the user is requested to close and reopen the
application. When the application opens, it looks to the LiveUpdate folder to
see if there is a newer version of the front end. If there is, it opens a
separate mdb file called VersionUpdateUtility.mdb and then closes itself. The
VersionUpdateUtility copies the newer front end from the LiveUpdate folder to
the main folder, then opens the copied front end and closes itself. Voila! New
front end open and functioning!
The great benefits of this system are that the clients can
easily see if their application is up to date. They can download and install
updates without having to think about where to unzip the files. That means less
support queries for me.
And I can prevent them from downloading updates if they have
not keep their subscription fees up to date. To do this I keep an encrypted text
file on my site containing all the clients usernames, registration numbers and
subscription status. When the client requests an update, the application first
examines the client’s file to see whether their subscription is current. If not,
no downloading is allowed. And all this without a web database!
Other Pages at VB123.com That You May
Want To Visit
Security And Passwords
Save Reports As PDF
Files From Access
The Workbench now
makes startup property management easier
Click on the
button for the next
help page in this Access Loop.
Further Posts on this topic
Just reading some of your tips. Your licensing idea that uses the disc serial
number sounds very useful. What's the API call to get the number? See
Also - in a Citrix or Terminal Server environment won't all users be running
on the same hard drive? You would need to combine this method with a user count
(concurrent and/or total) to limit the number of users.
My own method is to have an encrypted, workgroup secured mdb with the licence
number list in it. I make a couple of sales a year with that system so it's no
hassle to create new licences manually. To stop users passing on the licence
key, the Organization Name is also stored in the Licence.mdb - and this is
included in the footer of every report. It's a long way from bullet-proof I
know, but adequate in my case.
The original author wrote
I got this from Calvin Smith (http://www.calvinsmithsoftware.com/VolumeSerialNumber.txt),
but there are a number of other places where you can find similar code.
Private Declare Function
GetVolumeInformation Lib "kernel32" Alias _
"GetVolumeInformationA" (ByVal lpRootPathName As String, ByVal _
lpVolumeNameBuffer As String, ByVal nVolumeNameSize As Long, _
lpVolumeSerialNumber As Long, lpMaximumComponentLength As _
Long, lpFileSystemFlags As Long, ByVal lpFileSystemNameBuffer As _
String, ByVal nFileSystemNameSize As Long) As Long
'Author: Calvin Smith
'Environment: Visual Basic (32-Bit) MS Access (32-Bit)
On Error GoTo ReturnVolumeSerialNumber_Err
'32-Bit Usage: strRetVal$ = ReturnVolumeSerialNumber$("C:\")
strVolumeBuffer$ = String$(256, 0)
strSysName$ = String$(256, 0)
lRetVal& = GetVolumeInformation(strDrive$, strVolumeBuffer$, 255, lSerialNum&,
lComponentLength&, lSysFlags&, strSysName$, 255)
'If lRetVal& = 0 Then
'ReturnVolumeSerialNumber = "Unable to retrieve volume information"
'ReturnVolumeSerialNumber = "The VSN of " & strDrive$ & " is: " &
ReturnVolumeSerialNumber = lSerialNum&
If Err Then
'Do whatever you need to here!
Then I use the following line where ProgramPath is the
path to the current database ...
DiskSerialNumber = ReturnVolumeSerialNumber(Left$(ProgramPath, 3))
To use my system, I take the SerialNumber and do some
mathematical and string things with it, then display it on the clients order
form. The clients sends me this number which I unencrypt to find the
SerialNumber. Then I create a registration number which matches the serial
number in another mathematical and string algorithm and send it to the client.
They put in the registration number and it unlocks whatever functions you like.
I have several algorithms which equate to different versions of my application -
so if the client has a particular reg num, specific functions are allowed. The
reg num will only work on a computer where the algorithm matches the drive
This system has a number of potential disadvantages, and you can find
discussions of this on Access newsgroups. If a client buys a new computer, new
hard drive, formats the drive, and so on, a new number is required. This is not
a problem for me because I have a database of only about 150 registered clients,
so the new number requests are not that frequent. Also, I communicate directly
with the clients (phone or email) so can be reasonably confident that they're
not installing the system outside the licencing rules. And it's no great
hardship for the clients because I am usually quite prompt in providing a new
Also, not all drive numbers are unique, but in my niche market, there's not a
great problem with people trying to rort the system.