Audit Trail - Pull user name from table

[brharrii]

I am very new to working with VBA, so it is possible that I am going about this a harder way than is necessary.

Scenario: I am working in a product specification database and I've setup an audit trail to record any changes made through a form to my table. I've also setup a user table with a form that requires users to login to the database using a user name and password.

Objective: The code has a few bugs I'm working out one at a time. The big one I'm working on right now is changing the source of the audit trail code that records "username" to refer to the user name used to login to access. Currently it is using the windows user name which isn't ideal because not all users have unique windows logins (not something I can change unfortunately).

This is the code I believe I need to change:

& cDQ & Environ("username") & cDQ & ", " _

I havent been able to find a good way to change it though. I suspect it may be a slightly involved process. If anyone has an answer or any resources that I could refer to to learn more about this process it would be greatly appreciated. Thank you!

Also here is the code in its entirety incase that is helpful, Thanks again!

Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Long)   'Track changes to data.   'recordid identifies the pk field's corresponding   'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler   'Get changed values.
For Each ctl In frm.Controls
With ctl     'Avoid labels and other controls with Value property.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton, acToggleButton
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name         'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "tblUpdateAudit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& recordid & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"          'View evaluated statement in Immediate window.
 
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If
End Select
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:   MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub

 

[mdlueck]

Currently it is using the windows user name which isn't ideal because not all users have unique windows logins (not something I can change unfortunately).

That is really unfortunate.

The second best option would be to have the user's specifically login to the Access FE application. Perhaps still run on a known ID/pw in the code to connect to the SQL BE DB - necessary to check the userid table ja know! - but once cleared use that userid to know who is generating the activity. The downside to that is that is an extra sign-on... and defeats single sign-on.

Fortunately for me, there are no generic user accounts in the organization I serve with. ffffeeeewww!!!! I can simply grab the Windows UserID and run with that!

 

[brharrii]

Thanks for your response.

I'm not sure If I completely understand. So they would need to signin to the database twice? What would be the purpose of each signin?

Currently when a user opens a database, a form pops up that requests a user name and password. Is it possible to to use the username form that signon as the user in my audit trail?

 

[mdlueck]

I'm not sure If I completely understand. So they would need to signin to the database twice?

I read your post as some Windows accounts are generic / shared. Thus you can not use that as tracking means. So, have the users signin to the database application with a unique account.

Currently when a user opens a database, a form pops up that requests a user name and password.

By that I get the impression that you are already having users login a second time (Windows was the first time) to get access to your application.

And unique ID can not be guaranteed with TWO signins!?!?! If that is the case: Yikes!

Is it possible to to use the username form that signon as the user in my audit trail?

Have Access pop a "login form" ahead of making any connections which would automatically pop that box, collect the credentials, and use them on the user's behalf to connect to BE DB objects... supply that ID for them. Then Access will not pop credential requests during the use of the application.

 

[brharrii]

I read your post as some Windows accounts are generic / shared. Thus you can not use that as tracking means. So, have the users signin to the database application with a unique account.

ok, we're on the same page. Currently users sign into windows (not unique ID) and then sign into the database (unique ID). When access opens up it opens to a form that requests user name and password and then grants them access to the database if they supply correct credentials. What I don't know how to do is use those credentials in my audit log to record who is making changes to database.

 

[mdlueck]

What I don't know how to do is use those credentials in my audit log to record who is making changes to database.

Store said DB credentials in even a global variable / object which exists globally, and refer to said global entity to look-up what ID is generating the transactions when logging the activity.

If you are replacing Access security popups with supplying the credentials already, should be a simple matter to also have the ID available for audit log purposes.

 

[RainLover]

I would save the LogIn that is used for Access as a Global Variable.

Then I would use that Variable for my Audit Log.

 

[RainLover]

I am wondering why you are recording this Information.

Are you going to do it for every Table. It is going to be a lot of information that may not be of any use when you go to retrieve it.

How are you going to get access to these changes if someone needs it. Are you going to create a Menu system and a bunch of forms.

Are you sure users will keep their Logins a secret.

Maybe more trouble than what it is worth. I have used a much simpler method where I store the User ID and Change Date against the record. It was never used so I droped the idea.

Perhaps your situation is different.

Anyway just my thoughts.

 

[brharrii]

Thank you rainlover and mdlueck, Global variable I think is exactly what i was looking for, I just didn't know the word to describe it. Now to investigate Global Variables!

 

[brharrii]

Hey Rain Lover:

I am wondering why you are recording this Information.

Ultimately it comes down to that it is a requirement of a 3rd party audit that we participate in. They require that all changes be recorded along with who did them, what was changed, what it was changed to, and why the change was done.

Are you going to do it for every Table. It is going to be a lot of information that may not be of any use when you go to retrieve it.

It will be for one table.

How are you going to get access to these changes if someone needs it. Are you going to create a Menu system and a bunch of forms.

I'm in the process of designing a form that will show changes made by form to hopefully make the audit log a little bit easier to read.

Are you sure users will keep their Logins a secret.

No I'm not sure. It is a concern but I haven't come up with a solution to it yet.

Maybe more trouble than what it is worth. I have used a much simpler method where I store the User ID and Change Date against the record. It was never used so I droped the idea.

Hmmm, sounds interesting, how did you do that?

I appreciate your help and your questions to better understand my situation. it looks like for now I have a good lead with the global variable, that should be easy enough to look up tutorials on. Also if you think your other easier method may be applicable, I'd love to hear about it as well

Thanks again!

 

[mdlueck]

Thank you, brharrii. And I think the requirement to track who commits what records to the DB is quite reasonable. I do it in my applications.

 

[RainLover]

My Audit system does not meet your requirements.