vba code to capture the user's sql login name

I want to be able to capture and display the user's sql login name on my form when the user checks a checkbox called "InvestigationComplete" on my form.  Is there a way to accomplish this programatically.  I have admin writes to Microsoft SQL server.  The datasource is "ucrdata" sql table with a field called TestTech that I want to capture this login name.
Private Sub InvestigationComplete_Click()
Me.InvestigationComplete.Value = -1
Me.InvestigationComplete.Locked = True
Me.TestTech.SetFocus
me.TestTech.Value = ............user's sql login ID............
End Sub

Open in new window

sxxguptaAsked:
Who is Participating?
 
roger_karamConnect With a Mentor Commented:
Ah... well, I think that's been done. Try adding the funcion from Jalcadmim (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23630570.html)

me.TestTech.Value = GetSqlUser()


Function GetSqlUser() As String
Dim qrd As DAO.QueryDef, rst As DAO.Recordset

Set qrd = CurrentDb.CreateQueryDef("")
With qrd
.Connect = CurrentDb.TableDefs(0).Connect
.SQL = "SELECT SYSTEM_USER"
.ReturnsRecords = True
End With

Set rst = qrd.OpenRecordset()
GetSqlUser = rst(0).Value

rst.Close
Set rst = Nothing
Set qrd = Nothing
End Function
0
 
aikimarkCommented:
are you not using the Windows login for authentication?
0
 
sxxguptaAuthor Commented:
No, our IT group is not using this option as users may have the same generic windows login name (BTW...I am not in IT but in quality engineering)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
roger_karamCommented:
Hello. From what I've read, user for SQL Server is SYSTEM_USER.
You are however in a MS Access section...
0
 
sxxguptaAuthor Commented:
yes??
0
 
sxxguptaAuthor Commented:
again, how do I use vba code to capture the sql login id of a user?
0
 
roger_karamCommented:
Sorry, I guess what I meant to say is: where are you applying this code? in a VB accessing a SQL Server from excel?
0
 
sxxguptaAuthor Commented:
sorry for not clarifying

vba code accessing a sql server from Microsoft Access (version 2002) form.
0
 
sxxguptaAuthor Commented:
Awesome!!!!!!!!!  Thanks Roger:)
0
 
roger_karamCommented:
Thanks for the points sxxqupta!

Glad I could help, but credit goes to jalcadmin for the solution!

-RK
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.