Access Security

[dcorby]

Does anyone know if there is a way to set up Access security to use a windows login for the username and password? I have multiple applications, and distributing the front end can be a great problem with multiples of security for each.

 

[NoeNoa]

We actually do this here at our location using Access as an apllication front-end.

It's accomplished like this:

On start up we run a batch file that grabs the name/user login from the registry and saves it in a .txt file on the root. Then your application can grab the user name and use it in. MSAccess tied into a SQL server, has a table with these user names assigned to a value. That value is then used in the VBA of the front-end written in MSAccess to allow/deny access to certain forms, etc.



The batch File looks like this:
for /f "Tokens=2*" %%m in ('net user %USERNAME% /domain ^| find "Full Name"' ) do set fname=%%n

if exist User.txt del /q user.txt

@echo %fname% >> User.txt

After it is saved to the .txt file, and when Access is open it runs a quick macro to move the .txt file to a user table within Access. That Users table is cross referenced in our queries to an ODBC linked table in SQL that contains user values and is secured through NTSF permissions within MSSQL. Once we have the value of the user, we can use VBA such as the example below to enable and disable options created on the MSAccess forms we have built.

Example of the VBA:
Private Sub Form_Open(Cancel As Integer)
'Makes the form fill the screen.
DoCmd.Maximize
'Makes the Add New button visible.
Me!cmdAddNew.Visible = True

Dim stDocName As String
Dim stFrmName As String
Dim stLinkCriteria As String
'Checks to see if Permission variable has a value and,
'if not, set the calls the function to set the value.
If Permission = "" Then
Call SetPermission
End If
'Depending on what permissions are, allows users to do certain things:
'If permission to add SAP information
If (Permission = 1 Or Permission = 2 Or Permission = 4 Or Permission = 6 Or Permission = 7 Or Permission = 8) Then

'Button available
Me!cmdGroupChng.Enabled = True

'If No permission to make changes
Else

Me!cmdGroupChng.Enabled = False

End If


End Sub


I hope this helps.

Noe