Tuesday, 27 October 2015

Control Access options at start-up

From Access 2000 onwards, default behaviour is to have multiple windows open for multiple database components, i.e. if you open a database and then open a form and a report you'll have three Access windows open on your taskbar. Now I don't like this - it's messy and it spoils the illusion of your nicely packaged Access database looking like a standalone application. What you need is a way of enforcing the "one window" constraint.

Later versions of Access also have a function that enables object name changes to be tracked. This is great in a development environment but really should be turned off when your database is deployed for live use - you'll get huge performance gains by doing this. Of course, you can set these options before the database is deployed, but unless you have tightly secured your database what's to stop an inquisitive user turning it back on? What you need is a way of enforcing these options.

Fortunately, both these requirements can be met using VBA. You want this code to run at start-up, so I guess you could put it in the OnLoad event of a start-up form if you have one. Typically though, I prefer to write a function called fctnAutoExec and call this from an AutoExec macro using the RunCode action. This will then run at start-up by default. I often give fctnAutoExec a boolean parameter so that I can differentiate between it running at start-up or me running it by some other means and at some other time from within the database application. Anyway, fctnAutoExec might take the form:

Public fctnAutoExec(Optional booStartup As Boolean)
    'Ensure name change tracking options are turned off
    Application.SetOption "Log Name AutoCorrect Changes", False
    Application.SetOption "Perform Name AutoCorrect", False
    Application.SetOption "Track Name AutoCorrect Info", False
    'Ensure multiple windows in Taskbar are disabled
    Application.SetOption "ShowWindowsInTaskbar", False
    If booStarting Then
        'Do stuff that you want at actual start-up
        'i.e. things you might omit from ad-hoc runs.
        'E.g. write "logon" entry to user history table
    End If
End Function

So, to run the function from your AutoExec macro you'd just use the RunCode action with fctnAutoExec(True) as the parameter. To run the function on an ad-hoc basis from within your database, for example if you want to re-initialise settings but without doing any start-up only stuff, you'd just call the function as fctnAutoExec(False) or even just fctnAutoExec, since a Boolean data type defaults to False.

As a footnote, you should know that both the “track changes” and “show windows in taskbar” settings can be set manually before you deploy, using Tools, Options, General and Tools, Options, View respectively. This VBA does give you a nice way of ensuring your preferences are always applied though.

No comments:

Post a comment