MS Access – Splitting and Deploying Your Database – Part 2

In my previous post entitled Splitting and Deploying Your Database, I elaborated on the critical importance of splitting your database before deploying it to your end-users.  That said, many people post questions regarding securing their applications from their end-users to minimize their ability to mess around with the database, mainly fool around directly with the tables.  In this post I will elaborate on a few possible step you can take as a developer to best secure your database from what I qualify of ‘dangerous’ users.

There are a few things that you can do to try and harden your database against your users.  In 2 instances, you have the ability to take some steps using programming to secure your app.  In the 3rd, you can deploy your application in a secured method.  Let’s examine each.

 

Hide Object Pane & Disable SHIFT Bypass

As a developer, you can do your best to make it as difficult as possible for any user to gain access to any of your database objects (tables, queries, forms, reports, …) to pose a threat in the first place.  To do this you need to:

  1. Create an autoexec macro the uses RunCode to execute a VBA procedure at startup
  2. In your startup procedure add in the following code
If SysCmd(acSysCmdRuntime) = False Then
    DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide
End If

This code will hide the Access object browser (the pane that lists all the database objects: tables, queries, forms, reports, …).  If they can’t see them, then they can’t mess around with them!

  1. Since any knowledgeable user knows that they can bypass any autoexec macros by holding the SHIFT key at startup, we need to disable the shift bypass capacity.  Now there is no point in rehashing this subject, so please refer to: http://access.mvps.org/access/general/gen0040.htm

 

Convert to MDE/ACCDE

One more step you can take is to convert your database (mdb/accdb) to an (mde/accde) format.  By doing so you lockout the user’s access to the all the VBA code.  Furthermore, this creates a compiled version of the database which mean it should be optimized for use.  Win, win.  Just remember you cannot do development on an (mde/accde), so keep your original file (prior to conversion) for further development.

 

Deploy Your Database Using Runtime

The last thing you can do to restrict your users and limit their ability to run amuck in your database is to deploy your application using MS Access’s runtime version.  Instead of giving your user the full blown MS Access application, only install the free runtime version.  Unless, your user needs to do development in the database, there is no need for them to have the full version of MS Access.  By doing this, your user will not be able to edit any of the database objects!  They will only have access to whatever you have developed and given them access to.  Also, note that when deploying using the runtime version ensure you put in place error handling throughout all of your VBA procedures, otherwise when an error is raised, the application will blatantly crash.  You can freely download and install the runtime version of MS Access directly from Microsoft’s website at: 

MS Access Runtime 2007 -> http://www.microsoft.com/download/en/details.aspx?id=4438
MS Access Runtime 2010 -> http://www.microsoft.com/download/en/details.aspx?id=10910