April 26, 2005 at 3:37 pm
I need to populate a field in an Access 2000 form with the logged in SQL system user. In SQL I ran SELECT SUSER_SNAME () which returns the current logged in user. I need to get this data into my Access 2000 db. Has anyone encountered this before? Thank you.
April 27, 2005 at 1:13 am
Two part method. Create a stored procedure in SQL Server like
CREATE PROCEDURE dbo.ProcWho
@Who varchar(30) = "Not Known" OUTPUT
AS
SET @who = SUSER_SNAME()
RETURN
GO
Then in Access write bit of code to run this and get the results back e.g.
Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim user As String
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "ProcWho"
Set prm1 = cmd1.CreateParameter("RETURN", adInteger, adParamReturnValue)
Set prm2 = cmd1.CreateParameter("Who", adVarChar, adParamOutput, 30, "Not Known")
cmd1.Parameters.Append prm1
cmd1.Parameters.Append prm2
cmd1.Execute
user = Mid(cmd1("Who"), InStr(cmd1("Who"), "\") + 1)
Set prm1 = Nothing
Set prm2 = Nothing
Set cmd1 = Nothing
This is borrowed from an ADP, in an MDB, you would need to specify the Connection String more fully. The bit about instr is simply to remove the domain name from the front of the user name. You can then e.g. set the caption of a label to user or use a text box depending on what you want to do with it.
April 27, 2005 at 1:57 am
Or create a pass-through query in Access with the SQL
SELECT SUSER_SNAME() AS LoginName
You can then read the data from here as with any other query
Scott Doughty
--
Scott
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply