Storing the Current System User in SQL Server DB via Form

[Cheryl3D]

Hello:

I have another issue. I am working on a database project using a Microsoft Access Project (.adp) as a front-end and SQL Server 2000 on the back-end. I (and users) am using Windows NT authentication to log into the SQL Server database.

I have a main form that contains some sub-forms, and together they function as a data-entry form for entering new records into database.

On one of the sub-forms, I have a field called 'AdminID' located in a table called FundSites. This field records the current system user who entered the new record.

I have a code snippet below that refers to the BeforeUpdate event for the sub-form that contains the AdminID field in a text box on the sub-form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rst As Recordset
Dim strSQL As String


' Return reference to current database
Set cnn = Application.CurrentProject.Connection

strSQL = "SELECT * FROM dbo.FundSites"
Set rst = CreateObject("ADODB.Recordset&quot
rst.Open strSQL, cnn, adOpenForwardOnly, adLockPessimistic

Me![AdminID] = CurrentUser()
rst.Update
rst.Close
cnn.Close
Set cnn = Nothing
End Sub

Notice that for the AdminID field on the sub-form within the recordset, I state in the code:

Me![AdminID] = CurrentUser ()

This is the line of code that I what to change. Instead of the Access 2000 CurrentUser () function, I want the value to equal SQL Server's SYSTEM_USER function.

How can I make that happen? How can I change the ADO code above so that:

Me![AdminID] = the current system user in SQL Server 2000

Plus, the (SYSTEM_USER) value gets stored into the table when a new record is inserted.


I could use the SQL Server SYSTEM_USER function as follows:

SELECT system_user AS AdminID

It returns a value (e.g. domain name/user name). Correct? Could I use a stored procedure?


Any assistance would be greatly appreciated.

Thanks,
Cheryl

 

[Vinodk]

Why do you ant to come to your application to do this update? If you are using integrated authentication then you can fire a trigger at the backend for the FundSites that would update the AdminID field.

HTH,
Vinod Kumar