Navigation:  Articles > Jul-1998 >

Network Utilities

Previous pageReturn to chapter overviewNext page

Clyde M. Hauck II              
 
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.

Name

Type

Size/type/format

User ID

Number

Long Integer

UserName

Text

50

Net User

Text

50

Login Date

Date/Time

Short Date

Login Time

Date/Time

Short Time

 
 
Capturing the ID
The first routine I use can be found 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:
 

Declare Function wu_WNetGetUser% Lib "USER.EXE"  _

  Alias "WNetGetUser" (ByVal szUser$, lpnBufferSize%)

 

Function strNetworkUserID () As String

  Dim strUser As String * 255

  Dim intBufferSize As Integer

  Dim intStatus As Integer

 

  intBufferSize = 255

  intStatus = wu_WNetGetUser(strUser, intBufferSize)

  If (intStatus = 3) Then

     strNetworkUserID = "WNetGetUser Failed"

  Else

     'Return up to first Null.

     strNetworkUserID = Left$(strUser, _

             InStr(strUser, Chr(0)) - 1)

  End If

 

End Function

 
 
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:
 

Const MB_ICON_STOP = 16

Const ACT_ADD = 1

 

Function intLog (strTableName As String, varPK As _

  Variant, intAction As Integer) As Integer

' Log a user action in the log table

  On Error GoTo ahtLog_Err

 

  Dim dbCurrent As Database

  Dim rstlog As Recordset

  Dim rstLog1 As Recordset

  Dim strDte As String

  Dim strTme As String

  Dim intId As Integer

  strDte = Format$(Now, "ddddd")

  strTme = Format$(Now, "ttttt")

 

  Set dbCurrent = CurrentDB()

  Set rstlog = _

   dbCurrent.OpenRecordset("tbl_Login", _

    DB_OPEN_DYNASET, DB_APPENDONLY)

 

  rstlog.AddNew

     rstlog![UserName] = CurrentUser()

     rstlog![Net User] = strNetworkUserID()

     'rstlog![Tablename] = strTableName

     rstlog![Login Date] = strDte

     rstlog![Login Time] = strTme

     'rstlog![user id] =  a function to generate

     'a unique id

  rstlog.Update

 

  rstlog.Close

 

  intLog = True

intLog_Exit:

 On Error GoTo 0

 Exit Function

 

intLog_Err:

  MsgBox "Error " & Err & ": " & Error$, _

     MB_ICON_STOP, "intLog()"

  intLog = False

  Resume intLog_Exit

 

End Function

 
 
The intLogAdd routine calls the intLog routine:
 

Function intLogAdd (strTableName As String, _

  varPK As Variant) As Integer

' Record addition of a new record in the log table

  On Error GoTo ahtLogAdd_Err

 

  intLogAdd = intLog(strTableName, varPK, ACT_ADD)

 

intLogAdd_Exit:

  On Error GoTo 0

  Exit Function

 

intLogAdd_Err:

  MsgBox "Error " & Err & ": " & Error$, _

     MB_ICON_STOP, "intLogAdd()"

  Resume intLogAdd_Exit

End Function

 
 
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:
 

=intLogAdd("tbl_Login",[usrnme])

 
 
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:
 

Forms![Formname]![Fieldname]=strNetworkUserID()

 
 
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:
 

Declare Function GetUserName Lib "advapi32.dll" _

 Alias "GetUserNameA" _

(ByVal lpBuffer As String, nSize As Long) As Long

Declare Function GetComputerName Lib "kernel32" _

Alias "GetComputerNameA" _

(ByVal lpBuffer As String, nSize As Long) As Long

 

Function ComputerName() As String

 

Dim strComputerName As String

 

strComputerName = Space(255)

If GetComputerName(strComputerName, _

 (Len(strComputerName) + 1)) Then

   strComputerName = Trim$(strComputerName)

   strComputerName = Left(strComputerName, _

      Len(strComputerName) - 1)

   ComputerName = strComputerName

Else

   ComputerName = "ComputerNameError"

End If

 

ComputerName_Exit:

   Exit Function

 

ComputerName_Err:

   MsgBox Err.Description

End Function

 

Function UserName() As String

Dim strUsername As String  

 

On Error GoTo UserName_Err

 

strUsername = Space(255)

If GetUserName(strUsername, _

 (Len(strUsername) + 1)) Then

   strUsername = Trim$(strUsername)

   strUsername = Left(strUsername, _

       Len(strUsername) - 1)

   UserName = strUsername

Else

   UserName = "errorName"

End If

 

UserName_Exit:

   Exit Function

 

UserName_Err:

   MsgBox Err.Description

End Function

 
 
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):
 

Const MB_ICON_STOP = 16

Const ACT_ADD = 1

 

Function intLog(strTableName As String, _

varPK As Variant, intAction As Integer) As Integer

' Log a user action in the log table

  On Error GoTo intLog_Err

 

  Dim dbCurrent As Database

  Dim rstlog As Recordset

  Dim rstLog1 As Recordset

  Dim strDte As String

  Dim strTme As String

  Dim intId As Integer

  strDte = Format$(Now, "ddddd")

  strTme = Format$(Now, "ttttt")

 

  Set dbCurrent = CurrentDB()

  Set rstlog = dbCurrent.OpenRecordset("tbl_Login", _

      DB_OPEN_DYNASET, DB_APPENDONLY)

 

  rstlog.AddNew

     rstlog![User Name] = CurrentUser()

     rstlog![Net User] = UserName()

     rstlog![Login Date] = strDte

     rstlog![Login Time] = strTme

     rstlog![user id] = (intid + intid) + 1

     rstlog![Computer Name] = ComputerName()

  rstlog.Update

 

  rstlog.Close

 

  intLog = True

intLog_Exit:

  On Error GoTo 0

  Exit Function

 

intLog_Err:

  MsgBox "Error " & Err & ": " & Error$, _

     MB_ICON_STOP, "intLog()"

  intLog = False

  Resume intLog_Exit

 

End Function

 

Function intLogAdd(strTableName As String, _

  varPK As Variant) As Integer

' Record addition of a new record in the

' log table

  On Error GoTo intLogAdd_Err

 

  intLogAdd = intLog(strTableName, varPK, ACT_ADD)

 

 

intLogAdd_Exit:

  On Error GoTo 0

  Exit Function

 

intLogAdd_Err:

  MsgBox "Error " & Err & ": " & Error$, _

     MB_ICON_STOP, "intLogAdd()"

  Resume intLogAdd_Exit

 

End Function

 
 
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.
 
Read about the download NETUTILS.ZIP on this page
 
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.