Clyde M. Hauck II          Bronze Collection     
 
Here's a set of utilities that will let you capture who's doing what and when in a network database.
 
Over the years, I've developed several Microsoft Access 2.0 databases in both Microsoft Windows Networks and Netware-compatible networks. My customers required both secured and unsecured databases. In an "unsecured" environment, the current user of the database is always "Admin." In a "secured" environment, the current user has a username that can be used to determine security privileges. The username will be for a group that was created when the security model was established and that the user was added to when he or she was assigned privileges within that model.
 
I've discovered that most managers usually want a log that shows the specific individuals who have used the database and the date and time they logged in. In some cases, they've even wanted the ability to capture which recordset was used.
 
While Microsoft Networking and Netware have a lot of differences, the one constant in both networks is the Network Login ID that the user supplies when connecting to the network. To build this network log, I needed a solution that would capture the Network Login ID and let me use it within the database without requiring the user to input this information.
 
Many years ago, I found a snippet of code that fit the bill perfectly! However, it's been so long since I found this code that I can't give credit where credit is due. The code resides in two modules that I've saved in a "template" MDB for inclusion in all databases requiring the ability to capture the Network Login ID.
 
I found the most flexible way to store the Network Login ID, the current user of the database, and the date and time was to use a table, which I call tbl_log. This provides the maximum degree of flexibility in reporting, using the tool my clients are already familiar with -- Microsoft Access. The table layout is shown in Table 1.
 
Table 1. The log table format.

 
 
Capturing the ID
The first routine I use can be found in the module Mod_Auth_Netusr in the sample database available in the accompanying Download file. The function strNetworkUserId captures the Network Login ID information and returns it for your use:
 

 
 
The next two routines are in the Mod_Baslogging module. The intLog routine uses my Network Login ID routine and Access's CurrentUser function to capture the current user to add an entry to the log table. Although I no longer use all the code in the routine, I've left the routine basically intact in case I want to re-activate the code:
 

 
 
The intLogAdd routine calls the intLog routine:
 

 
 
Capturing the data
In all of my databases, I routinely supply a splash screen on startup that gives the name of the database, along with revision levels and creator information. Rather than spend the time developing my own splash screen, I found one that's flexible and easy to use (and royalty-free!) in The User Construction Interface Kit from Cary Prague Books and Software. Since this form is only accessed once on the startup of the database, it's the ideal location to capture the information and store it in tbl_login.
 
With the Cary Prague splash screen, the method to accomplish this is quite simple. Place an unbound field on the form and name it usrnme, then set the forecolor and backcolor to match the form's background. This hides the field from the user (setting the field's Visible property to False would also hide the field, but this would also make the field unusable). On the form's Unload event, enter the following:
 

 
 
Now, each time the database is opened, the Network User ID, the Current Username, and the date and time are captured and added to the table `tbl_Login'. This information can also be passed to a routine to retrieve user ID information from some other source (a table of employees, for instance). The table's information can then be used for managerial reporting functions as defined by your customers. The usrnme field can be used to hold any arbitrary information that you want to pass to the logging routine. As you can see, I currently don't make use of it.
 
Now that you have a method for capturing the Network Login ID, it can also be used to keep track of when an individual adds data to a specific recordset. I normally accomplish this by creating a text field in the desired table to hold the Network Login ID. Then, when I develop the forms that display the recordset, I add a field and set its GotFocus event to:
 

 
 
As the individual enters data and tabs to this field, the Network Login ID is automatically populated and included in the recordset. (You can experiment with this to determine the best fit for your individual needs).
 
Upgrading
These procedures have worked extremely well for all of my Access 2.0 applications. Now comes my dilemma: My customers are migrating to both Access 95 (version 7.0) and Access 97 (version 8.0), and this code will no longer work. Conversion of the Mod_Auth_Netusr module failed because of the 16-bit API calls within. I needed a solution that was quick and easy to use.
 
I finally found my solution in a great book by F. Scott Barker called Access 97 Power Programming. With a little modification to the original code, I was able to create the same results I was getting in my Access 2.0 databases. I was even able to capture one more piece of useful information: the workstation that the user was at when he or she logged in.
 
The new, 32-bit version of Mod_Auth_Netusr now captures the computer name along with the username and replaces the 16-bit version of Mod_Auth_Netusr. Here are the two routines it contains (ComputerName(), UserName()), along with the necessary API declarations:
 

 
 
The routines in Mod_Baslogging are slightly different from the module's 16-bit versions. Instead of setting rstlog![Net User] to NetworkUserID(), the field is now set to UserName(). And, of course, the computer name is also captured (you'll have to add the field [computer name] to the table tbl_login):
 

 
 
Because I didn't change the names of my routines in the conversion from 16 to 32 bits (or change any of the parameters), the code in my forms and splash screen didn't need to be changed. So now that my dilemma has been resolved, I've included these 32-bit "templates" in my collection for use when needed. Both versions of the routines are in the accompanying Download file, and I hope that you find them as useful as I have.
 
Download NETUTILS.ZIP
 
Clyde Hauck is a systems engineer for a large outsourcing company, as well as the president of his own PC hardware and software consulting corporation, which specializes in custom software designs.