Msaccess User

This is an "all-or-nothing" approach that will screen users based on network User Name when they try to open the database, but it won't control access to forms, tables, queries, etc., within Access.

In your database, store the VBA code listed below in a module. Then create an Access Macro called "AutoExec" -- this will automatically fire when the database opens. Define the AutoExec macro to run the "AutoExec_Function" function. I know this is convoluted but it's the only way to get Access to run VBA code on startup.

=============================================
VBA code follows
=============================================
Public Function AutoExec_Function() As Boolean
'
' this must be a Public Function to be called by the Autoexec macro
'
Select Case GetNetworkUserName()
' list approved network User Names here
Case "user1", "user2", "user2"
Case Else
MsgBox "Sorry, you are not authorized to open this database."
CurrentDB.Close
End Select
AutoExec_Function = True
End Function ' AutoExec_Function

Public Function GetNetworkUserName() As String
Dim sNetworkUsername As String
Dim wshNetwork As Object
On Error GoTo LeaveFunction
sNetworkUsername = "" ' default
Set wshNetwork = CreateObject("WScript.Network")
sNetworkUsername = wshNetwork.UserName
LeaveFunction:
Set wshNetwork = Nothing
GetNetworkUserName = sNetworkUsername
End Function ' GetNetworkUserName

Hi,

Thanks for your help I added VBA Code which you have provided me but now when I am trying to open MSAccess Database it is not asking me any username and password.
Can you please help me to set this code in MSaccess Macro.
If possible can you provide me some more steps to set this macro in msaccess.
Thanks.
Anurag.

RE: "...but now when I am trying to open MSAccess Database it is not asking me any username and password."

That is correct -- this code will not ask for User Name or Password. It only checks the network User Name of whoever is trying to open the database, and compares it to the list in the "Select Case" statement in the VBA code. For example if an authorized user logs into your network as "BGates" then you would need to add "BGates" to the list in the VBA statement. For everyone else, the database will not open.

Something occurs to me: did you add your own User Name to the list before saving and closing the database? If not, you may have effectively locked yourself out. You can probably get it to open using the "/x" command line parameter, to tell Access to run a macro other than AutoExec when it opens.