Microsoft Access Front-End Security. Part 1: Locking the Front End

In this week’s post I am assuming that you have split your database into two distinct files: one for the front end, which is what your users interact with, and one for the back end, which is where the data are actually stored. There are several things you can do to make you data and database more secure. I will discuss three of them here. First there is protecting the back end and your VBA code, Second is requiring a password to open the database, third is limiting certain functions to certain people.

Locking down the back end and VBA Code. By default anyone using Microsoft Access, even with a split database, can open up the tables and work directly in it. They can also open the VBA editor and alter the code. To prevent this you need to lock down the front end.

Important! Be sure to make a back-up copy of your deployed production front end and store it in a different directory. There may well be a way to unlock a front end, but I don’t know what it is. It’s safer to have a back up anyway.

In Access 2007 you lock down by going to the Office button, then select “Access Options” at the bottom. In Access Options choose “Current Database”. In Access 2003 go to “Tools”, “Start up”, “Advanced”. Now, unselect “Use Special Keys”, “Enable Layout View…”, “Enable design changes…”, “Allow Full Menus”, and “Allow Default Menu Shortcuts”. Then click “OK”.

When the database is next opened no user will be get to the back end tables except through the forms and will not be able to alter the forms or the VBA code.


Next week: Part 2: Requiring a password to open the database.

SQL failing in VBA due to apostrophes

The most common reason an SQL statement fails for when I try to run it via VBA, using DoCmd.RunSQL, is that I forget to account of apostrophes or single quotes. This is easly fixed by using the replace() function.

'Pick up the value for your variable.

strSearchTerm = Me.txtSearchBox

'Then run the variable through the replace() function, 

'replacing each single quote with two single quotes.

'This tells the parser to ignore the single quote.

strSearchTerm = Replace(strSearchTerm, " ' ", " ' ' ")

'You can also do this using the ASCII designation for a single quote, chr(39).

strSearchTerm = Replace(strSearchTerm, Chr(39), Chr(39) & Chr(39))

Tagging Container Lists for EAD Guides

The front matter for EAD guides has not posed any problem for me as we create MARC records for all our collections. I then use Terry Reese’s MarcEdit, with the style sheet modified for local practice. MarcEdit is freeware and you can download it from:

http://people.oregonstate.edu/~reeset/marcedit/html/index.php

The container lists were another problem. We have many that are over 100 pages and I did not want to have to tag them all by hand. What I did instead was create a Microsoft Excel to EAD conversion tool, which I named Ead McTaggart. Rather than explain it here I will refer you to my article “Ead McTaggart: Using VBA to Automate EAD Container List Tagging” in issue 8 of Code4Lib Journal:

http://journal.code4lib.org/articles/2025

The article includes a link to the complete VBA code for the tool.

Smart Fields on a Microsoft Access Form

I set up a Microsoft Access database for students to create folder lists for collections. The only data I needed was collection number, box, folder, folder title, date, and notes. The data entry form only needed those six fields. I used unbound text boxes for the fields and in the on-click in the VBA behind the form the values of the text boxes were picked up as variables. An SQL statement then wrote those values to the table. To minimize the amount of data entry the students had to do I used sticky and smart fields on the form.


The folder number is a smart field, and the box number is sometimes a smart field. As noted above when the “Next Folder in Box” button is clicked the folder number text box is not reset to null or zero-length string It is instead set to the folder number variable value plus 1. However if the “Next Box” button is clicked the folder number text box is set to 1 and the box number text box is set to the box number variable plus 1.

What this all means is that if you have

Collection: 5544
Box: 12
Folder: 14

And click “Next Folder in Box” the form will show

Collection: 5544
Box: 12
Folder: 15

However, if you click “Next Box” the form will show

Collection: 5544
Box: 13
Folder: 1

So, once those three fields are filled in at the beginning of a data entry session they never need to be touched again for the entire session, no matter how many folders or boxes are processed.

Here is the VBA code I used:

Dim intBox as integer             ‘Variable to hold the box number.

Dim intFolder as integer          ‘Variable to hold the folder number.

intBox = Nz(Me!txtBox, 0)             ‘Pick up the box number from the form,

                                                   ‘convert to a zero if null.

intFolder = Nz(Me!txtFolder, 0)       ‘Pick up the folder number from the form,

                                                   ‘convert to a zero if null.

‘Code to write the form data to a table would go here.

‘When all tasks are done, reset the form.

‘If “Next Folder in Box” was clicked, add one to the folder number.

intFolder = intFolder + 1

‘If “Next Box” was clicked, add one to the box number

‘and reset the folder number to 1.

intBox = intBox + 1

intFolder = 1

Me!txtBox = intBox              ‘Set the box number control’s value on the

                                           ‘form to intBox.

Me!txtFolder = intFolder        ‘Set the folder number control’s value on the

                                           ‘form to intFolder.

Sticky Fields on a Microsoft Access Form

I set up a Microsoft Access database for students to create folder lists for collections. The only data I needed was collection number, box, folder, folder title, date, and notes. The data entry form only needed those six fields. I used unbound text boxes for the fields and in the on-click in the VBA behind the form the values of the text boxes were picked up as variables. An SQL statement then wrote those values to the table. To minimize the amount of data entry the students had to do I used sticky and smart fields on the form.

The collection number is a sticky field, and the box number is sometimes a sticky field. After the data is entered in the form if the “Next Folder in Box” button is clicked the value of each field is picked up as a variable and written to the table. Then the text boxes on the form for collection number and box number are set to the value picked up as the variable for the field. The other text boxes, except folder number, are set to either null or zero-length string.

Here is the VBA code I used:

Dim strColl as string               ‘Variable to hold the collection number.

strColl = Nz(Me!txtColl, "")   ‘Pick up the collection number from the form, 
                                          'convert to zero length string if null.

‘Code to write the form data to a table would go here.

‘When all tasks are done, reset the form.

Me!txtColl = strColl               ‘Set the collection number control’s value on the form 
                                           'to strColl.

Introduction to the Programmable Archivist

The intent of the blog is to present some ideas and solutions I have come up with for using technology in archival processing. The hope is that you, the reader, will be able to make use of some of my ideas, suggest other ideas or options, and point out ways to make my programming better. From time to time I will certainly present questions, rather than solutions in the hope that some reader will have the solution I need.  I am not a programmer by training and programming is not the main component of my job, but I do some programming in order to make my job as a Technical Services Archivist easier and to assist the Reference Archivists. What I include here will not always, or even usually, fit the technical definition of programming. I simply intend to document some of what I do to facilitate archival work. Most of what I include on this blog will be for Microsoft Access and VBA. On occasion I may include Microsoft Excel, EAD, XSLT, and whatever else comes to mind.