Getting Access Username to SQL Trigger (1 Viewer)

[Pauldohert]

I ahve a trigger which fills in an audit table. It currently uses the SQL server system_user to record who has changed records. (using windows authentification) . Can I also pass across the access username, so that can be used by the trigger and recorded to the audit table.

THanks

 

[namliam]

The trigger is auto triggered the moment you change things from Access, dont think you can send the username from access to be used in the Trigger? That is unless you write said username also into the table that is being changed.

 

[kentgorrell]

If you have a table (let's call it tblCurrentUser) that stores both system_user and your user name from the FE that is updated or inserted when the user opens the application, you should then be able to use this in your trigger.
you can be the System User in the FE with

Public Function System_User() As String
    With CurrentDB.CreateQueryDef("")
        .Connect = [Your_Connect_String]
        ' Call the SS Function
        .SQL = "SELECT System_User"
        System_User = Nz(.OpenRecordset().Fields(0), 0)
    End With
End Function

then probably use a DAO recordset to insert or update when the user logs in

a table like this, linked to the FE is also useful to show who else is in the BE if you update the FE User Name to null when your applicaiton closes which i would do in the close event of a form that always stays open until the applicaiton quits

in the trigger you would then use something like
Edit_By = (SELECT FEUser_Name FROM tblCurrentUser WHERE SystemUser_Name = (SELECT SYSTEM_USER))

This should also work for bulk updates which could be better than trying to update this column in the before update event of a form.

 

[Pat Hartman]

I'm not sure this is possible with a trigger. The trigger is running on a server NOT on your local machine. Somehow, the server would need to be able to get the logged in user from whatever machine sent the request.

 

[Isaac]

I ahve a trigger which fills in an audit table. It currently uses the SQL server system_user to record who has changed records. (using windows authentification) . Can I also pass across the access username, so that can be used by the trigger and recorded to the audit table.

THanks

Create a table with at least 2 columns. TableName and uUserName. Allow only one record per unique combination of user and tablename.
Before updating the table in question, update the record in this table with the tablename(s) in question, and the username performing the action.
THEN run the query/code to update the table(s) in question.
In your trigger, grab the value from this table and do whatever you want with it.

Alternately, create a reference table with the sql usernames vs. the corresponding access usernames, if that's possible in your case. Then refer to that in the Trigger.

 

[Pat Hartman]

The userID has to come as part of the data in the update query. In a multi-user environment, you can't rely on a static value in a table.

 

[Isaac]

The userID has to come as part of the data in the update query. In a multi-user environment, you can't rely on a static value in a table.

It depends on the workflow and only the OP and his business can know whether that might work, but I agree, that's a concern.

 

[kentgorrell]

I'm not sure this is possible with a trigger. The trigger is running on a server NOT on your local machine. Somehow, the server would need to be able to get the logged in user from whatever machine sent the request.

if @Pauldohert is currently getting the correct System_User using a trigger then a table in the db which is updated by the FE when the user logs in with the System_User and whatever their user name is in the FE should be able to lookup that user name using System_User to populate the audit table in the trigger.

and...
I just did a test in a multi user environment. I opened an Access application in a RDP environment that connects to a SQL Server instance on another server using windows authentification. Then used the function I posted earlier.
System_User does return the credentials of the user that sent the request.

Is there any circumstance why this would not be the case?

 

[theDBguy]

if @Pauldohert is currently getting the correct System_User using a trigger then a table in the db which is updated by the FE when the user logs in with the System_User and whatever their user name is in the FE should be able to lookup that user name using System_User to populate the audit table in the trigger.

and...
I just did a test in a multi user environment. I opened an Access application in a RDP environment that connects to a SQL Server instance on another server using windows authentification. Then used the function I posted earlier.
System_User does return the credentials of the user that sent the request.

Is there any circumstance why this would not be the case?

Good discussions; but just a reminder, this is a 12-year-old thread, so I hope the OP already found a working solution for them by now. Cheers!

 

[kentgorrell]

Good discussions; but just a reminder, this is a 12-year-old thread, so I hope the OP already found a working solution for them by now. Cheers!

well spotted DBguy, but here's the thing... I have a project where i was thinking of using this method and it is going to be critical. So in the spirit of resuse/recycle it seemed fitting to use a thread that already posed the same question and expand on it.

In this case not only will I be updating a column, Project ID, using triggers based on the current user but will also be filtering Views on this same column. and as we know you can't filter a view on a parameter sent from the FE. But as users select a single project to work on when they open the application, I can capture the selected Project ID and use it in triggers and views.

And because I have to deal with others messing with the UI design, I need a methodology that ensures this column is populated reliably even when someone runs an insert query. ** I can't keep em out of object design views in Access but I can control what happens in SQL Server **

And I'd rather be proved wrong now than when I'm well into replacing their multitude of Access BEs (one for each Project) with one SQL Server Db.

The only caveat I can think of is that a user can have only one instance of the application open on the same device.

 

[Pat Hartman]

if @Pauldohert is currently getting the correct System_User using a trigger then a table in the db which is updated by the FE when the user logs in with the System_User and whatever their user name is in the FE should be able to lookup that user name using System_User to populate the audit table in the trigger.

Do what you want. You can never be 100% certain that the value in the table is the value you need when you are operating in a multi-user environment. I would not use this method.

 

[kentgorrell]

Do what you want. You can never be 100% certain that the value in the table is the value you need when you are operating in a multi-user environment. I would not use this method.

Pat, why do you think this might not work in a multi user environment? is it because System_User may not be reliable? Or because of the possibility of the same user opening more than one instance of the application? or is there some other reason?

 

[Pat Hartman]

The table will contain the value last placed there. Computers are very fast and multi-threaded. Between the time you update the table with the name you want and update some table activating the trigger, someone else could have placed a different name in the table. I'm not saying that the method won't work. It will work unless it doesn't and you'll never know. It is unreliable because it assumes an uninterrupted scenario which is not in your control. If you find a way to wrap the whole series of updates in a transaction, the process would be reliable. All update actions within a transaction live or die as one. If anything fails, all get backed out. The transaction would also enqueue the "name" table which would prevent any other thread from sneaking in an update in the middle of your update process.