4

I would like to know if there is a way to get system username and use it directly in an MS Access query. I have made a parameter work within a query from a combo box on a form and I have also acquired system name in Access VBA using ENVIRON ("USERNAME").

Kindly let me know if this is possible.

Manus
  • 869
  • 2
  • 10
  • 20

4 Answers4

10

You need to create a VBA function that returns the username, and then use the function in the query.

Public Function GetUserName() As String
    ' GetUserName = Environ("USERNAME")
    ' Better method, see comment by HansUp
    GetUserName = CreateObject("WScript.Network").UserName
End Function

and

SELECT foo FROM bar WHERE myUserName = GetUserName();
Andre
  • 26,751
  • 7
  • 36
  • 80
  • 4
    `Environ("USERNAME")` can be spoofed easily. A more secure alternative is `CreateObject("WScript.Network").UserName` – HansUp Sep 14 '15 at 16:01
  • Thank you Andre451 and HansUp. This answers my question perfectly. I was not aware of Environ beinf spoofed easily. will replace it with CreateObject in my code. – Manus Sep 15 '15 at 08:40
1

My solution kept all the work in VB.
I used a variable for the windows login username and then created a SQL string with that variable inserted. Lastly, I updated the query behind the form to use this new SQL string.

The CHR(34) puts quotes around the name as it is now a string inside the SQLSTR and needs to be within a set of quotes.

If you have a complex SQL statement, write it in the QBE using a string for the name and all the other variables, then switch to the SQL view and replace it using a VBA variable as shown below.

MyName = Environ("username")

sqlstr = "SELECT * From Projects WHERE ( ((Projects.LeadEngineer)=" & Chr(34) & MyName & Chr(34) & " AND ActiveYN = True ));"

Forms![Main Form].RecordSource = sqlstr
F.Lazarescu
  • 1,385
  • 2
  • 16
  • 31
Michael
  • 11
  • 1
0

You can use SYSTEM_USER if the query is being executed in a SQL Server, that will retrieve the user name connected to the database (for that, make sure you are not using fixed user name in your connection string)

igorjrr
  • 790
  • 1
  • 11
  • 22
-1

Yes - you can use the 'CurrentUser' function in your query. Here I've included it as a field and criteria.

SELECT Field1, Field2, [CurrentUser] AS UserName FROM Table1 WHERE Field1 = [CurrentUser];
jhTuppeny
  • 820
  • 1
  • 11
  • 16
  • 2
    `CurrentUser` is the Access username, though (typically "admin" if not specified in the Access command line), not the system (Windows) username. – Andre Sep 14 '15 at 13:18
  • Yes you're right - I thought he was after the current user. – jhTuppeny Sep 15 '15 at 08:10
  • Hi Tuppeny, yes i was getting Admin when I executed that query. My aim was to get the name of the user who was logged on. – Manus Sep 15 '15 at 08:39