the musings of an ancient geek

I have been working with an old Access 2000 application and decided, due to the change in use to a Kiosk type application, where multiple people use the same application without shutting down, that the security mechanism based on Workgroup security will have to go. This application had previously been secured by using a bespoke workgroup file, where the original Admin user had been deleted and a new Administrator user had been added to act as the overall administrator.

I tried the mechanisms that I found by searching for it, namely to give privileges to everything to the user group but it failed at the point where you are supposed to import the application into a newly created database – telling me, not unreasonably, that it was secured by a workgroup file that I hadn’t provided.

The breakthrough came when I was reading about the security provided by Access 2007, and in particular the new accdb file format. User level security no longer applies to these file formats and Access removes any security it has applied when you convert to this database format.

So the process turns out to be simple. Just follow the following steps (using Access 2010 – I assume earlier versions will have equivalent options):-

1. Start Access up as normal for a secured file (ie specify the workgroup file on the command line)
2. Select the file tab and chose the “Save and Publish” option. Select the file format for the accdb format and click on the large “Save As” button
3. When the conversion completes (mine took about 5 minutes – it is not an immediate process) and the file is saved in the new format close down Access
4. Restart access without a work group file and create a new blank database in the the earlier Access file format (I am using Access 2000 format)
5. Use the “External Data” tab and select an “Access” filetype to import from
6. Import all objects from the file that you saved in the accdb format

The only issues I have had with that process so far are

• Some of the reports were set up for a printed not on my current development machine. I will have to re do the printer setup for those
• I was missing the references to ADO and DAO objects, so I had to manually re-add them (from Visual Basic, Tools, References menu).

I recently learnt a neat trick for calling a form in Microsoft Access and retrieving values from it. It is really nice to encase the whole thing in a function which retrieves a return value.

Let me show you with a simple example – I want to return an address based on a post code lookup.

First, we need to define an new Type to hold addresses. All address formats are different, but this is mine

Public Type Address
    valid As Boolean 'true of remainder of record holds a valid address, false otherwise
    hseNoStName As String
    address1 As String
    town As String
    county As String
End Type

We then have a function to do the lookup

Public Function getAddress(postcode As String) As Address
    Dim returnAddress As Address
    DoCmd.OpenForm "frmPostCodeSearch",,,,,acDialog,postcode   'call form as dialog,with postcode as openArgs
    If CurrentProject.AllForms("frmPostCodeSearch").isLoaded Then
    ' We found an address, so return it
        returnAddress.valid = true
        returnAddress.hseNoStName = Forms!frmPostCodeSearch.hseNoStName
        returnAddress.address1 = Forms!frmPostCodeSearch.address1
        returnAddress.town = Forms!frmPostCodeSearch.town
        returnAddress.county = Forms!frmPostCodeSearch.county
        DoCmd.close acForm "frmPostCodeSearch",acSaveNo
    Else
        returnAddress.valid = False
    End If
    getAddress = returnAddress
End Sub

I won’t go into the full detail of the post code search as its quite hard. The important bit is what to do if no address matches – So there is a click on an “Abandon” button

Private Sub Abandon_Click()
    DoCmd.Close acForm,"frmPostCodeSearch",acSaveNo
End Sub

And what happens when the user double clicks on field in the record selected

Private Sub hseNoStName_DblClick()
    Me.Dirty = false 'ensures record is not locked
    Me.Visible = false 'causes dialog to return to calling function
End Sub