Need to know who in your business added a particular record? Or who updated it last? It’s a pretty common requirement, but unfortunately not one that Microsoft Access has built in functionality for. Simply adding a “last updated by” field to your table can (in many cases) be enough to meet most needs, but just how do you know who updated the record if Access can’t tell you? A little VBA code is all you need, as this article will show you.
No need to create your own “login” form to capture user name
Unless your database has its own login form or mechanism, then the only way you’ll be able to know who is currently using/logged in to the database is to use one of the Windows Operating System files – specifically the “mpr.dll” file. If you’ve not used external dll’s in your code before, it can seem a little daunting, but as you’ll see in the code below, it’s actually pretty straight forward.
So – let’s define what we want our code to do…
“Let me have the name of the currently logged in user”
Firstly, the code:
Declare Function GetUser Lib "mpr.dll" Alias "WNetGetUserA" (ByVal Name As String, ByVal UserName As String, Length As Long) As Long Public Function GetUserName() As String Dim returnStatus As Integer Dim Name, UserName As String 'Allocate space for user name to be stored in UserName = Space(255) ' Call the aliased function in the mpr.dll file. returnStatus = GetUser(Name, UserName, 255) If returnStatus = 0 Then 'Strip the username from the returned string UserName = Left(UserName, InStr(UserName, Chr(0)) - 1) Else ' An error occurred. GetUserName = "Error retrieving the user name." End End If GetUserName = UserName End Function
The code, explained
The first thing you’ll see is that we’re making a reference to an external function, contained within the mpr.dll file. We do this by declaring a local name (GetUser) for the public function contained in the dll (in this instance, WNetGetUserA). – we also define what parameters are necessary for the function call – in this case the “Name”, “UserName”, and “Length” parameters. The only one we’re really interested in here is “UserName” but the remainder have to be supplied too as that is how the function is set up within the dll.
Now that has been done, we create our own function, from which we’ll be making a call to this external function. The code for our function is pretty minimal but let’s run through the key elements:
- “UserName = Space(255)” – if you step through the code in the debugger, what you will see is that when the “GetUser()” function is called, the “UserName” variable that we pass to it actually gets overwritten. In order for that to happen, the variable needs enough memory allocated for the data to be stored in, so we just allocate 255 space chars to the variable as that should be more than enough for most (if not all) user names to be contained in.
- We capture the return value from the function call in the “returnStatus” variable. A zero (0) here means the call was successful, anything else means an error has occurred.
- Finally, the “UserName” variable needs tidying up as it will be essentially padded with null chars so we extract the chars that are in the variable before a Chr(0) is found.
See – now that wasn’t that bad was it! And now, at least if you have to deal with mdb database corruption, you’ll know who was using the database last so who you can blame! (I’m joking here of course!)
Author Introduction:
Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL Server problem and excel recovery software products. For more information visit www.datanumen.com