Presentation and sample files at : MSAccessInitialisation20071008.zip

Microsoft Access is an exciting product and it is understandable that a developer would want his application to move immediately to his attractive Main Menu, deafened by the applause of his grateful users.

Well just hold your horses here, pardner. Let's not be impetuous. Shouldn't we first ensure that we are going to give our users a smooth ride?

On my travels, I have often had to troubleshoot the applications of other developers, when the application suddenly stops working after the developer has left site. It is not that the applications were in error; it is just that they did not check and diagnose before running. It was not the developer's fault that the customer moved the database, changed the version of Access or switched the PC to Japanese.

There is no hard rule on this, as applications are so different, but for illustration, how about checking that the following are OK? :

This is not a code-based presentation. API calls with be mentioned but not illustrated. MS Access code fragments will be used for examples. I'll address the mainly the pre-2007 world, although it will get the odd mention.

Once an application is opened, the application will run the Start-Up form or AUTOEXEC macro, if they exist.

The Startup form can be found by viewing the application in Design mode and selecting Tools - Startup.

Although not a fan of Macros, because of their lack of effective error handling (prior to Access 2007), my applications always have just one Macro, which is the AUTOEXEC macro. (OK, two macros including the Hourglass Off macro, for when my code crashes during debugging)

With an AUTOEXEC macro present, it is obvious to anyone supporting your application where your application starts. In the case of initialisation, it is also a good place to put your checks as it presents a clear list to those unfamiliar to your work of art.

1         Application

1.1  Instance

It is not uncommon for a user to open the same application twice. Of course, as a Developer, you have tested your application for this? No? What number of data nightmares await ? Two sessions editing the same record, …or sharing the same temporary table……or changing different parts of the same process.

 Don't give yourself unnecessary work; prohibit multiple instances of the application. 

One such way to do this is by a code module mdlCheckMultipleInstances written by © Graham Mandeno, Alpha Solutions, Auckland, NZ (graham@alpha.co.nz). Using the Windows API

This module and its variants can be found at : http://www.utteraccess.com/forums/showflat.php?Board=8&Number=1493272

 If a second instance of your application is initiated, the following message will appear :

When OK is selected, the second instance closes and focus is returned to the original instance.

 The function winCheckMultipleInstances() returns a True is there is a multiple instance. This can be implemented in the AUTOEXEC macro by setting the Condition to winCheckMultipleInstances() and the Action to Quit. 

1.2  Splash Screen

The splash screen is your application identifier. When this appears, it is important that your application has not yet accessed the data sources. It may be that the user has accidentally started the application when they are not a user. They may have no permissions to access your data sources. It would not be a good first impression that your application crashes when they open it.

A splash screen should be simple, clearly identifying itself and giving the user a clean way to access the application. It also acts as a gateway to your Main Menu.

 It is also a good place to display details of the User's environment. This could be useful to a helpdesk if the application crashes beyond the Splash Screen.  

It is not helpful for a developer to build an application where, when an issue occurs from startup, it just crashes. 

Information could include parameters such as :

 The Screen Resolution function could be used dynamically with your application. For instance, if you have built the application for 1024 X 768 use, and a User has a  wide screen format where the height is only 600 pixels, then your forms could switch the vertical scrollbar on, where appropriate. It also helps when the User just says that they can't see the buttons.

1.3  Mode

It may be that specific users may only use your application in a particular mode, such as read-only, or to be connected to a specific data source. 

A shortcut may have target of a number parts such as : 

<This is where Access is> <This is where the application is> <This is a variable I am passing>

 An example could be :

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\TestApp\TestAppVersion_1.mdb" ;Database1 

This is not a secure method. The passed variable is accessed as the system Command variable :

The above code will only allow a user who opens the application directly to access the Development database. This is also convenient for the Developer during development and support. Otherwise it initialises the connection variables with the correct data.

1.4  Menu Bars and Ribbon

1.4.1       Menu Bars

When you have a bespoke application, you will probably not wish generic toolbars to be visible, as you may not wish to offer powerful sub-menus such as File or Edit. You have probably built custom Menu Bars to support your application. 

Most toolbars can be switched on or off with the ShowToolbar macro. Common toolbars to switch off include Form View, Formatting (Form Report), Printer Menu, etc.

 If you wish to maximise the real estate on the screen then you can hide the default "Menu Bar". We cannot hide it by name, as it varies with internationalisation. Up to Access XP, this is CommandBar 3. After that it is CommandBar 4. Pass this function the Enable/Disable flag and it will Hide/Show the "Menu Bar" : 

1.4.2       Ribbons

 To select what ribbons include, in Access 2007, on start-up, go to  : 

The Office button cannot be removed. 

For more information visit http://www.accessribbon.de/en

The Office button and ribbons, as pretty as they may be, take up valuable real estate. 

1.5  Application Properties

Some application properties can be set in your distributed file manually, or set during initialisation. These include :

 These can be set by selecting the options in the database container manually, or setting it in code from an external application or internal pug-in. 

Tools-Options-View

Tools - Options - Edit/Find

These can be read/changed with the Application.GetOption and Application.SetOption methods. 

 1.6  Application Version

In the case of a backend only serving the application, then the backend database password would be changed when there is a change of application version. 

However, for operational reasons, this is not always possible. In that case it is desirable for the application to check that it is the correct version, by looking at a variable in the backend database.

2         Environment

2.1  Access Version

As each new version of MS Access provides new functionality, it can compromise the application if it runs under an earlier version that does not support it. This is not limited to the file format of the application, as it may be converted to another file format. 

 The SysCmd() function can return the "numeric" value of the MS Access version. It should be noted that it always has a dot separator, irrespective of international format, which cannot always be directly converted to an integer. The above function can be used to display the MS Access version on the Splash Screen. It can also be used in the next function. 

 This function can be used in the AUTOEXEC macro. As the function returns a False if not the correct MS Access version, then we must set the Condition to NOT winCheckMultipleInstances() and the Action to Quit.
 

2.2  Access Settings

An application cannot assume that the user's copy of MS Access is set to allow the application optimum operation. This can include :  

 The changing of properties, which are part of the application, should be decided by the Developer. However, the changing of MS Access properties could affect other applications of the user. It is always better to read the settings at initialisation and restore then when the application quits.

 It should be noted that many of these changes are only checked at startup, so may not be effective until the next time the application opens.

2.3  Date/Time

The following can be displayed on the Splash Screen. 

This will usually be a good indicator of internationalisation, and confirms the accuracy of the Date/Time of the local PC. Of course, your application should be robust to the ravages of internationalisation. But, if you were told that the application is only used in the UK, and a User sends you a screenshot of this in Polish, it can alert you to potential issues. 

The PC clock is also worth monitoring. I have had users deliberately moving the PC clock back a couple of days to give themselves more time to work, resulting in a compromise of the whole process. This can be reinforced by adding a function that does not allow them to be out of phase with the server. For instance, with a UK server the following function allows for users only between Japan and the East Coast of the USA.  

This function can be used in the AUTOEXEC macro. ServerDLookup() is your own function to retrieve date/time from the server. As the function returns a False if not the correct MS Access version, then we must set the Condition to NOT CheckLocalTime() and the Action to Quit.

3         User

3.1  User Id

The Network User ID can be displayed on the Splash Screen

 It is also a good way to identify a User. It should be noted that this should not be used as a method of security, if "spoofing" is possible. It would not be secure if :

1.      A User could not get a copy of the backend. They could then copy it onto their own PC and set their Network User ID as a privileged user.

2.      A User cannot build a duplicate User ID on the network. Most managed networks will not allow a User to build a new User ID, let alone a duplicate User ID.  

The User ID can be found using Environ("UserName")

3.2  User Privileges

After checking who the User is, then it is a good time to check what the User may do. This is usually attained by configuring User Roles. 

This may result in different buttons or menu options being visible. 

Roles could be : Administrator, Manager, Auditor etc.

4         Security

If you have chosen MS Access security, then there is little for an initialisation process to do. Access security itself will ask for User ID and password before your initialisation has started. Personally, I prefer not to set Access security, and have that function controlled by the application itself.

4.1  Linked Tables & SPT's

Linked tables and Pass-Through-Queries betray connection UID and Passwords in their connections strings. Using ADO can greatly improve this situation, although it cannot be used in all situations.

4.2  Correct host machine

By storing the PC ID of each User, we can perform another check. This would detect unauthorised use. 

In three-tier applications, such as running your application on a Citrix server, then all Users would have the same PC ID; that of the Citrix Server. If it was different, then someone is trying to run the application elsewhere.

 The host computer name can be found using Environ("ComputerName")

5         Data

5.1  Existing Attachments

When an application opens, the existing attachments cannot be guaranteed, and present a security risk.  

An early task can be to delete these attachments. Function DetachExistingAttachments() is called from the AUTOEXEC macro and in the ExitApplication() function.

5.2  Data Connection

Only once the Continue button is selected from the Splash Screen should the data source be connected and tested. 

For instance, function ConnectToBackend() tests the existence of the subordinate \Data directory and the backend database AccessInitialisation_DB.mdb. In the event that they may be missing, or moved, the User can select the new location.

5.3  Testing Data

If there is going to be a problem with your application manipulating data, then it is better to check it during initialisation, rather than waiting for the user to crash it.

 Checks could include include :

 Function CheckBackendDatabase() resolves some of this.

 Even more important are the checking of attached spreadsheets. Access applications often attach to spreadsheets, which are oh so easy to change by Users and can have columns reformatted or the order and number of columns changed.

5.4  Exception Reporting

Even with the correct data secure and connected, it may not have the quality to ensure successful operation. Not all of the data is controlled by your application. Quite often it is not until a new system is used, are the flaws in the data exposed.

 For instance, you have a drop-down box of Managers. When a Manager is selected, their Department is displayed. But when you select Fred Bloggs, the application crashes. This is the first time that you learn that not all Managers have their Department selected. It may not be just a case of improving your error handling, but it means that the process does not work for some managers. 

One way to help is with Exception Reporting. As types of data anomaly are discovered, they can be added to an Exception Reporting mechanism. This can list them in a report to Administrators. If you have made an administration application to accompany your User application, then make the report appear every time the Administration application appears. This should pester the Administrator to do something about it.