X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Db Admin Messages, Any Version    
post May 29 2014, 04:55 PM

UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin

This is a fairly simple concept that I thought others would find useful. Basically, this is a simple way of getting notified when certain things happen with one of your database frontends. It can be anything, from the date/time a troublesome user logs in, or when a duplicate record is detected in a table, or whenever a certain report is being run, and by whom. Rather than use an E-Mail, this method updates a table with a customized message, along with the date/time the message was sent, and which computer it originated from.
So, first - set up your table for storing these messages. Pick one of your backends that your frontends can link to and put it there. You don't need a copy of this table in each backend.
AdminMsgID [Primary Key, Autonumber]
dteDateCreated [Default Value: =Now(), Date/Time]
strComputerName [Text]
strMessage [Text]

Next, in the frontend where you want to send messages FROM, add a link to this table. Then add a new Standard Code Module to the Frontend where you want to send messages from, and add this code:
Option Compare Database
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
                    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
'   Note: The fOSUserName function was taken from
'   The Access Web: http://access.MVPs.org/access/api/api0008.htm
'   Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
        fOSUserName = vbNullString
    End If
End Function

Public Function ComputerName()
'   Return the computer name
    ComputerName = CreateObject("wscript.network").ComputerName
End Function

Public Sub SendAdminMessage(strMessage As String)
    Dim strSQL As String
'   Add a new record to the Admin Messages table
    strSQL = "INSERT INTO tblAdminMessages ( strMessage, strComputerName ) VALUES ('" & _
             Replace(strMessage, Chr(34), Chr(34) & Chr(34)) & "','" & ComputerName & "');"
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

Public Sub CheckMessages()
'   If Dennis Kuhn is logged on as the user...
    If fOSUserName = "dkuhn" Then
'       Check to see if there are any messages..
        If DCount("*", "tblAdminMessages") > 0 Then
'           If there are, display them.
            DoCmd.OpenTable "tblAdminMessages"
        End If
    End If
End Sub

THere are the contents:
fOSUserName - A function that grabs the current network username.
ComputerName - A function that grabs the current computer name.
SendAdminMessage - A subroutine that takes a message and writes it to the admin messages table you created.
CheckMessages - A subroutine that opens up the admin messages table for you to view.

Note that you'll want to customize the last subroutine so it checks to see if YOU are logged in, not me.

At this point, you can now easily send a message from the frontend. Let's say you want to know every time the Annual Report is opened. Go to the report and add this code to the Open event:
SendAdminMessage ("Annual report opened by " & fOSUserName & ".")

Or maybe you have some existing code where you have a MessageBox that informs the user to contact the administrator. Now, instead, just send it to yourself:
SendAdminMessage ("Dupe record in BBR table for sample " & rst.Fields("strLoginSampleNo"))

As you can see, the SendAdminMessage subroutine accepts a string for the message you want to send. It appends a new record to the table that includes the computer name and your message. The date/time field automatically fills in with the date/time the record was created.

Now, to actually VIEW these messages, just add a call to the CheckMessages subroutine to whichever event you like. For example, let's say you are always opening up the Billing database, which includes a Switchboard form that opens automatically whenever the database is opened. Just add this to the Switchboard form's Open event code:

If any records exist in the messages table, the table will be opened for you to view. If you want constant updates, you can set up a timer event that calls this subroutine every hour, every five minutes, or whatever works for you. If you want to get fancy, you could create a form that displays the messages, and have CheckMessages open that form instead of the table. But this is the basic skeleton of the concept that you can build off of. For example, you may feel like you need to hide this table if your users are able to access the Navigation Pane.

All of these concepts should work just fine in any version of Access, although I daresay it's safe to assume this won't work with Web applications.

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 10:20 AM