Capturing user who changes database

Question

I work on a few projects with a front end recently moved to vb.net, the back-end's remain in access .accdb format.

With an Access front end I was able to set up some data macros that captured the username of a person making changes and record them in an audit table, it looks like it will be a little harder with a .net front-end.

Is there any way to pass this information from the front end to the database ? Or is there a method of making the back-end look at the function that is stored there (It doesn't seem to work at the moment)

This is the code I have in the back

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

Public Function GetUserName() As String
' 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
        GetUserName = Left$(strUserName, lngLen - 1)
    Else
        GetUserName = ""
    End If
End Function

I have a public function doing the same in the front-end, also called getusername()

Have you looked at `Environment.UserName` for the logged in user's details? — Andrew Mortimer, Jul 14 '15 at 16:34
The function works to get the username, I just need the access backend to get that information — David Burnside, Jul 14 '15 at 18:00

score 0 · Answer 1 · answered Jul 15 '15 at 00:36

My friend, here we're talking about principal differences between working within enclosed environment of MS Access, not just as database but also the application. Since everything inside Access is native to itself, you have access to all the internals. Now you moving away from that model and your design should change accordingly. Here you are in, lets call it client-server environment, and client knows little besides connection string and server knows nothing about client.

This is not an issue how to retrieve user name or some other information from code. Your goal is to design a system which will support auditing. You have audit table already - this is good.

Depending on your security model, you either use user name of the machine user, or user name of the application user. But you have it - in VB it is easy.

Without knowing your Audit structure, I can tell that the way it is usually done, when data is changing, you create Sql transaction using Ado.Net, and under this transaction you insert you audit data and then you do your CRUD on real data. Then you commit. This way, you either succeed or fail both - no data modified without audit.

Now, another step is to add to your table (if you don't have already) 4 fields:

CreatedBy, CretedDate, ModifiedBy, ModifiedDate.

Each record will be ready to display who was playing with your system and when.

And this is how your Access Db gets the user:

via sql that contains user column and data "insert into ... values('" & user & "', ...."
via separate audit call that contains user name
via stored procedure where 1 parameter can be dedicated to pass user info

Capturing user who changes database

1 Answers1