Get system username when opening MS Access

[incognito41]

We have a shared 2007 MS Access DB here at work and I would like to run a script that runs everytime someone opens the file. I would like the VBA script to get the user's username and append a log table with that info and with the date and time. I tried looking this up have only found uses for IE. Can anyone help with this? any help is greatly appreciated.


thanks,

 

[JaredJames]

You can use the Environ() function.

[String] = Environ("UserName")

I've heard there are problems with sandbox mode in Access 2003, I haven't tried it with 2007 so not sure. You might want to look into this.

Sample Code:

Function RetrieveUser()
YourString = Environ("UserName")
End Function

 

[incognito41]

thanks, i think i just need to figure out how to add a record to a table and set the value to environ("username")

 

[JaredJames]

What part are you stuck with?

I have just done something very similar today so may be able to help you with exactly what you are looking for.

 

[incognito41]

I figured it out. I used VBA to submit a SQL command since there seems to be more help for SQL than there is for VBA. I created a macro with the script below and then call it ON LOAD. Thanks for the help!

Function Run_Log()

Dim mySQL As String
Dim Name As String
Name = Environ("UserName")
mySQL = "INSERT INTO tbl_user_log ( Username, _Date )"
mySQL = mySQL & " VALUES ('" & Name & "' , Date() &' ' & time() )"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

End Function