Demonstration Databases For Registered Users Of The Workbench

When you purchase the Access Workbench, you will receive a number of demonstration databases that can improve how you run your database. These include internal logging of user details, how to shutdown your database and send messages to your users and how to setup and retrieve the version information for your database. These will be illustrated now.

Sending Messages To Your Users and Shutting Down The Database In An Orderly Manner
Applies to Access 2000 upwards

Once the users of your database grows past a handful, informing people about what is going on with your database becomes a chore. More difficult than that is the task of shutting down a database when your users are scattered over different floors or even different buildings. Add to this mix is the problem of people logged into a database that are in meetings or out buying their lunch and you will find that an orderly system for shutdowns is important. The Workbench is designed to help you out by automating messages and shutdowns.

To achieve this, the Workbench is required to work in two parts. First there is the Workbench itself which prepares a file that holds the user messages or the shutdown message. Working in tandem with this is a form that must be setup in your database that looks for the existence of these message and shutdown files and acts appropriately.

{Click on picture to view the Workbench side of this process}

Setting Up A Version Numbering System For Your Database
Applies to Access 2000 upwards

One important part of writing professional software databases is to maintain a system of version details for the software that you create. These version numbers allow you to to verify that correct versions of software have made it into user environments and they also allow you to wind back and to recover from issues that have been created by changes in the design of your software databases.

When you purchase the Access Workbench you will receive a copy of the software that you need to display the version information on the main form of your database.

Figure 1 - Demonstration database showing you how to setup version information

Internal Database Logging Of Windows User IDs and Login Times
Applies to Access 2000 upwards

As it is not feasible on most networks to restrict the usage of a computer to a single person, the computer name and Access workgroup username provided by the Access Workbench, may not be enough information to track down an individual. The computer name also makes it difficult to find a user when they use a database on a variety of computers. To reduce this confusion, it is good practice to log the Windows user ID as they open the database. Other information that is useful to log is the Access workgroup username and the time the person opens and closes the database.

Notes: A Windows User Id is the name that is used to log on to a Windows 2000, Windows XP, NT or Windows 98 network or computer. An Access workgroup username is the name that you select when logging into Microsoft Access. If you do not select anything, you will generally be logging in using the Admin username.

The demonstration database includes a form (see Figure 2) that demonstrates what objects you need to log user activity in your database. This form also demonstrates how to log usage of individual Access forms and reports.



Figure 2. The demonstration database shows how to log users and object usage.

What you will need to import into your database to make the demonstration examples work.
- UserLogs table
- UserAdmin table
- UserObjectLogs table
- fxl8_UserLogs form
- fxl8_Startup module
- qryGR8_UsersLoggedInNow query
- rptGR8_UserLogs report
 

Working example: Shutting down twenty users

A database that I’ve been working on for the last three years has about twenty-five different users every day who either make changes, or run reports and query information. When I have to sort out problems, or upgrade the software, the first task is always to ask all the users to log out of the database. After a bit of ‘polite shouting’ across the desks, we generally reduce the list down to about five remaining users. These people can usually be found at meetings or buying a coffee downstairs. To find out who these users are and where they are located, I look at the log of Windows user IDs in the database and filter the list down to the names of those who’ve not logged off during the day. This gives me a few Windows 2000 User IDs that I can give to the manager to find out where they sit. Then we try and contact those people and when all else fails we go to their computers and shut them down. To handle this situation in a more sophisticated manner, we use the Administration tools of the Access Workbench.

Initiating a User Log
To generate details that log when the user opens and closes the database, I open an Access form called frmGR8_UserLogs (see Figure 3). This startup AutoExec macro opens the frmGR8_UserLogs as a hidden form. This is illustrated using Visual Basic as follows

DoCmd.OpenForm “fxl8_UserLogs”, , , , , acHidden
 

Figure 3. Shows the simple interface of the user logging form that will be hidden from users.

The form load event in the form FXL8_UserLogs collects information for the log using visual basic as follows.

Dim sqlStr As String
Const dateTimeFmt = "dd-mmm-yyyy hh:mm"

Private Sub Form_Load()

Dim UserNameStr As String, ComputerNameStr As String
dim LoginTime As Date, wkgUserStr As String

Me.visible = False

' Whilst this form is important, it should not interfere with the user interface
' That is why there is a quick exit

On Error GoTo Quick_Exit

UserNameStr = User_FX
ComputerNameStr = ComputerName_FX
LoginTime = Now
wkgUserStr = CurrentUser

The Visual Basic shown above gathers information using the functions provided in the demonstration database:
- User_FX which finds the Windows User ID using system API calls
- ComputerName _FX which finds the computer name using system API calls
- CurrentUser which returns the current workgroup username

Now that we have that vital information, we need to save it to the UserLogs table (shown in Figure 4). An SQL insert statement generates the log when the user opens the database (using code deliberately not shown).

Completing The User Log
Of importance is the key field in the UserLogs table which comprises of the Windows user Id and the time. The key field value is first stored in a hidden text field on the form. We then reuse that key field value using the form close event to update the time that the person logged out .

Private Sub Form_Close()

' Update the current login with a logout time"
' We always want this to complete so we turn off error messages and warnings.
' This should only happen when the database is closing down normally.

... ( code deliberately not shown)

End Sub

You may be curious as to how we guarantee that the form is closed whenever the user shuts down Microsoft Access. Thankfully, Access will close all open forms when it shuts down and the close form event will be fired. If the database is shutdown by an unforeseen event such as a power failure, the open forms are not closed properly and the UserLogs table will not have an entry in the LogOffTime field (see Figure 4).
 

{Click on picture to view}
Figure 4. The Userlogs table stores the system name, access workgroup ID, computerName and the time that a person logged into and out of the database.

To view this information you can either go directly to the UserLogs table or you can view the information in a query or a report. The rptGR8_UserLogs report (see Figure 5) summarizes the number of times that a person has opened a database (logged in), when they last opened it and if they still have the database open. This is quite useful for administration purposes.
 

{Click on picture to view}
Figure 5 A report showing Window User ID Information

The final piece of administration that I like to handle with this frmGR8_UserLogs form is to close down the database at midnight using the form timer event (code not shown):

Hint: User logging works best if you manage your Access startup properties to ensure greater user compliance. Garry Robinson's very practical book on Access Database Protection and Security as it has lots of related information. Read more

Other advanced Workbench articles

Here are some other articles that come with the Workbench.  Read about them and get the download database when you purchase the Workbench.

Advanced Logging of Access User Details

Extracting Your Access Software Configurations

Display release details for "your Access database"-- the easy way

Other Access Workbench Topics
Home Page | What is an LDB File | Advanced Ways To Start The Workbench   | Orders

The Access Workbench Help File  - Internal Database Logging