Find current user in access/sql

[duns]

Hi,

I am having trouble assigning the current user to a control. I am using access front end and sql backend.

I want to have a control do something based on who the user is. In access I can use currentuser(). This usually gives me Admin. I can also insert the current sql server user into a database i.e. using a sql statement. But I can't get the current sql server user to display in my access form. Any ideas?

 

[cmmrfrds]

In SQL Server the user name is retrieved from.
suser_sname()

select suser_sname()

 

[duns]

Hi cmmrfrds,

I have got the sql server username. What I can't get it to do is display that value in a control on the access form. Any ideas?

 

[cmmrfrds]

I have a stored procedure to return the login to the Access program.

CREATE Procedure sp_getLoginName
@userSA varchar(30) output
As
select @userSA = suser_sname()
return

------------------------------
ACCESS CODE TO EXECUTE SP

Dim userSA As String
Dim cmd As New ADODB.Command
Dim param1 As Parameter
userSA = "none"
' Connect
Set cnn = CurrentProject.Connection

' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cnn
cmd.CommandText = "dbo.sp_getLoginName"
cmd.CommandType = adCmdStoredProc

' Set up input parameters.
Set param1 = cmd.CreateParameter("output", adVarChar, adParamOutput, 30)

cmd.Parameters.Append param1
cmd.Execute
userSA = cmd(0)

------------------------------------------
YOU CAN THEN

me.controlname = userSA