Solved

vba code to capture the user's sql login name

Posted on 2011-02-21
10
504 Views
Last Modified: 2013-11-27
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
Comment
Question by:sxxgupta
  • 5
  • 4
10 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 34947218
are you not using the Windows login for authentication?
0
 

Author Comment

by:sxxgupta
ID: 34947232
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
 
LVL 5

Expert Comment

by:roger_karam
ID: 34947617
Hello. From what I've read, user for SQL Server is SYSTEM_USER.
You are however in a MS Access section...
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:sxxgupta
ID: 34947915
yes??
0
 

Author Comment

by:sxxgupta
ID: 34947917
again, how do I use vba code to capture the sql login id of a user?
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34948125
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
 

Author Comment

by:sxxgupta
ID: 34948159
sorry for not clarifying

vba code accessing a sql server from Microsoft Access (version 2002) form.
0
 
LVL 5

Accepted Solution

by:
roger_karam earned 500 total points
ID: 34948179
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
 

Author Closing Comment

by:sxxgupta
ID: 34951215
Awesome!!!!!!!!!  Thanks Roger:)
0
 
LVL 5

Expert Comment

by:roger_karam
ID: 34954506
Thanks for the points sxxqupta!

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

-RK
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question