Returning User Name 1

[papernate]

I want to get the current users name, much like SUSER_SNAME() does in SQL (it doesn't work for what I am doing). I know about CurrentUser(), but that just returns "Admin", because I don't have a secured workgroup file. The backend and security is handled by SQL Server. Is this possible to do with VBA?

 

[jsteph]

Use this declare:
Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

********** then use below fcn
Public Function GetUser() As String
'gets OS user, (like Eviron())
Dim l As Long, sUser As String
On Error Resume Next
sUser = Space$(255)
l = GetUserName(sUser, 255)
'strip null terminator
If l <> 0 Then
sUser = Left(sUser, InStr(sUser, Chr(0)) - 1) 'null terminated string
'below due to app. specific. dest. field size limit
sUser = trim(Left$(sUser, 50))
GetUser = sUser
Else
GetUser = &quot;<Not Found>&quot;
End If
End Function

--jsteph

 

[papernate]

Thank you, I'll try it as soon as I get back to work on Monday. I'll let you know how it works.

 

[papernate]

I really appreciate the help, but it doesn't do what I need. I should have been more clear in my previous post. The function you provided gives me my windows log-in.

What I need is the log-in name that I use to log into the database.

 

[RiderJon]

I am learning VBA, so I may be off. Is this what you were looking for:

' It displays the user name of the current user
' of the database
MsgBox(&quot;Current user is: &quot; & CurrentUser)


RiderJon
&quot;I might have created ctrl+alt+del,
But Bill made it famous&quot; - Dr. Dave

 

[jsteph]

papernate,
<<What I need is the log-in name that I use to log into the database.>>

Now I'm really confused--if this is what you want then currentuser() will do it--if it returns Admin, then that's who logged onto Access--the default if no .mdw is used.

If your backend is sql, then that system's function will be what you need--I'm not sure what backend you're using--I'm guessing sql7 or 2000-, so suser_sname will return the login user--that is whomever logged onto the current session (in the case of Access, that would not necessarily be the odbc-table link--it would depend on the session from which you execute the procedure. I.E., you could link sql2000 tables with any number of user names, and then you could execute procedures with either ado or pass-thru, and the ado connection will have it's user and the passthru could have a completely different user, all could be different from any linked tables.
-jsteph

 

[papernate]

Ok, my apologies for not being clearer.

I am running SQL Server 2000. Thank you for stating my problem better- it's whomever is logged into the current session that I need. 'suser_sname' doesn't work in VBA. I do use it quite a bit in SQL commands, and it works perfectly. Do I need to enable a particular reference to make it work in VBA?

 

[Norris68]

Ah! I understand. The solution would be to create a recordset to query the server. Paste the following into a public module ...

Public Function SUSER_SNAME() As String
Dim R As ADODB.Recordset
Set R = New ADODB.Recordset
R.Open &quot;SELECT SUSER_SNAME() As SQLUser&quot;, CodeProject.Connection, adOpenStatic, adLockReadOnly
SUSER_SNAME = R(&quot;SQLUser&quot.Value
R.Close
Set R = Nothing
End Function

(You may want to add some error checking )

 

[papernate]

Thank you! Works perfectly.

 

[papernate]

ps - Have a star

 

[Norris68]

Thanks