• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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

0
sxxgupta
Asked:
sxxgupta
  • 5
  • 4
1 Solution
 
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
 
roger_karamCommented:
Hello. From what I've read, user for SQL Server is SYSTEM_USER.
You are however in a MS Access section...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
roger_karamCommented:
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now