Manage Users and permissions Access 2010

Hi

Ok I found the code and understand that bit. I have created form1 and added a text box and in the source, what do I out in to get the name Delilah to show.

I have tried =Forms!FrmLogin!CboUser but I get a name error in the box.

Any ideas

Thanks
 
Yoiu dont need to refer to the form jut refer to the StrUser variable
 
Hi

In the control source of the text field in From1 I have tried

[StrLoginName]=Me.CboUser
StrLoginName=Me.CboUser
=Me.CboUser
=CboUser

and all of them come back with
#Name?

any ideas please

Thanks
I am definately logged in as Delilah
 
If You have a textbox on your form called Me.WhoAmI then On the form OnLoad event

Me.WhoAmI = strLoginName
 
Hi

I have attached the database with the form. I ahve named the text box mewhoamI and added onload an event with me.whoamI=strUserName

I am now getting compile errors.

Is it possible you could adjust the form correctly (Form1) and then i can understand better.

many thanks
 

Attachments

Take a look at this revision I have added a text box to the form FrmSessions call TxtWhoAmI

Look at the On Load event to see how I populated it
Also look a the after upate event of the combo box on the login screen to see how I passed the users name to the public variable StrWhoAmI
 

Attachments

Hi

I cannot open 2010 on this laptop, any chance it can be saved as 2003 version. I am doing this for 2010 but wont have that machine until Tuesday.

Thanks as always
 
Hi

That is excellent, I also applied it to my Form 1 just so I understood it complete and it works in that as well now. So a big thank you. My thinking now is this...

To apply this to the real database, I;

1. Copy all tables , forms and code from the example DB.
2. Then I add in a Job postion field to the table users and adjust all user names and set passwords.
3. Then (and this is the last bit of the jigsaw I think) how do I link it to my quote table or report query, so it shows Name and Job Title....

So its I think it is just number 3 I will need further advice on.

Thanks
 
Hi David

Could you explain how I can add a user and password into the database. I added my name into the user table and I added userid (13) into the permission table and wrote in my password.

When i try and log in I get a runtime error.

Could you advise please.

Thanks
 
Hi David

I left the username blank in the tbl-permissions and then it asked me to create a password in the login form. This works ok.

In the TBL users it keeps the name Colin and on the forms we did yesterday it shows Colin, so thats good.

In the table permissions it changed the name Colin to Administrator. Could you explain why and how the tables work together.

Thanks
 
This code has been very helpful, however I could use some advice on how I can track this login though. What I am trying to do is once the Agent clicks login and their Id and password are validated, I want it to store the agentID and the date/time of login into a table labeled storedlogins. I also want it to naviagte to my home page, Can I just use a simple macro to navigate me there, or do I need to write it out? I am not real good with coding I am a intermediate user. any advice or examples would be really helpful.
Thanks
 
Hi David

I tried applying the example we had to the real DB but could not get it working. i took myself through this tutorial and created a similar situation.

http://www.about-access-databases.co...-box-form.html

and I created a login screen and it works, apart from I cannot see who is logged in. I am using Access 2010.

I am not up on coding so really need a simple way to;

See the user full name and the job position details and apply them to a quotation report...

Sorry if I have gone a little bit backwards but would really appreciate some help from yourself or anyone else reading this post...

Thanks
 
I have code that identifies the user based on Windows Login and matches PW and accessibility to Active Directory. We then take it ONE step further and match to a table in the DB for switchboard and user/admin permissions based on login. So the user has a login to the DB Server and then a login to the actual DB in Access.

Code for login verification:

Option Compare Database
Option Explicit
' Note: If at all possible use the code for the Active
' Directory Login. This is more secure and easier
' to maintain.
' These variables are populated by the password code and
' are mainly used for security within the databases.
'User Name for Menu and Code for Who Added the Rec?
Global UserName As String
Global UserCode As String
Global IsAdministrator As Boolean
Global ProjectName As String
Global MySwitchboard As String
Global MyLogNo As Long
' !!!! Code for Passwords if Active Directory is available
'Variable to get the url for active directory
Public gstrLDAPURL As String
'Password setup
Global Password As String
Global VerifyPassword As String

Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long
Const NoError = 0 'The Function call was successful
Public Function GetUserName() As String
' Buffer size for the return string.
Const lpnLength As Integer = 255
' Get return buffer space.
Dim Status As Integer
' For getting user information.
Dim lpName, lpUserName As String
' Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)
' Get the log-on name of the person using product.
Status = WNetGetUser(lpName, lpUserName, lpnLength)
' See whether error occurred.
If Status = NoError Then
' This line removes the null character. Strings in C are null-
' terminated. Strings in Visual Basic are not null-terminated.
' The null character must be removed from the C strings to be used
' cleanly in Visual Basic.
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
GetUserName = lpUserName
Else
GetUserName = "Unknown"
End If
End Function
Public Function Authenticate(strUserName As String, strPassword As String) As Boolean
On Error Resume Next
Dim conLDAP As ADODB.Connection
Dim strSQL As String
Dim strLDAPConn As String
Dim rsUser As ADODB.Recordset
Dim arrDesc

Set conLDAP = New ADODB.Connection
conLDAP.Provider = "ADSDSOOBject"
strSQL = "Select AdsPath, cn From 'LDAP://" & gstrLDAPURL _
& "' where objectClass='user'" _
& " and objectcategory='person' and" _
& " SamAccountName='" & strUserName & "'"
conLDAP.Provider = "ADsDSOObject"
conLDAP.Properties("User ID") = strUserName
conLDAP.Properties("Password") = strPassword
conLDAP.Properties("Encrypt Password") = True
'open connection + password
conLDAP.Open "DS Query", strUserName, strPassword
'execute LDAP query
Err.Clear
Set rsUser = conLDAP.Execute(strSQL)
'rs will be empty if authentication fail

Authenticate = False

If Err.Number = 0 Then

If Not (rsUser Is Nothing) Then

If Not (rsUser.EOF And rsUser.BOF) Then
Authenticate = True
End If
End If
ElseIf Err.Number = -2147217865 Then
MsgBox "Error in LDAP settings" & vbCrLf _
& "Call Admin"
End If
End Function
Function CheckAccess() As Boolean
' This function checks the users access to this database
If IsNull(DLookup("[Menu_Group]", "[UserLog]", "[User ID]= '" & UserName & "' and [Database]= '" & ProjectName & "'")) Then

CheckAccess = False

Else
MyLogNo = DLookup("[LogNo]", "[UserLog]", "[User ID]= '" & UserName & "' and [Database]= '" & ProjectName & "'")
MySwitchboard = DLookup("[Menu_Group]", "[UserLog]", "[LogNo]= " & MyLogNo)
UserCode = DLookup("[StaffID]", "[UserLog]", "[LogNo]= " & MyLogNo)
If DLookup("[Administrator]", "[UserLog]", "[LogNo]= " & MyLogNo) < 0 Then
IsAdministrator = True
End If
CheckAccess = True
End If
End Function
' !!!! Code for Passwords when Active Directory is NOT Available
Function Encr(In_Text As String) As String
Dim I As Integer
Dim Out_Num As Long
Dim Out_Text As String
On Error GoTo Encr_Error
Out_Num = 1

For I = 1 To Len(In_Text)
Out_Num = Out_Num + Asc(Mid$(In_Text, I, 1))
Next I
Encr = CStr(Out_Num)
Encr_Done:
Exit Function
Encr_Error:
Warning Error$, "Encr"
Resume Encr_Done
End Function

LOGIN Form Code
Option Compare Database
Option Explicit

Private Sub Command21_Click()
On Error GoTo Continue_Error

If Authenticate(UserName, Me.PassW) = True Then
'Try removing the toolbar again
DoCmd.ShowToolbar "Form View", A_TOOLBAR_NO
DoEvents
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE UserLog SET UserLog.[Last Login Date] = Date(), UserLog.[Last Login Time] = Time() WHERE UserLog.LogNo = " & Str([MyLogNo])
DoCmd.SetWarnings True

DoCmd.Close A_FORM, "Login"
DoCmd.OpenForm "Switchboard"
Else
Warning "Please, check your password and re-enter it. ", "Password Check"
End If
Continue_Done:
Exit Sub
Continue_Error:
Warning Error$, "Command21_Click"
Resume Continue_Done
End Sub
Private Sub Command22_Click()
DoCmd.Quit
End Sub
Private Sub Form_Load()

' Sets up default value for carriage return/line feed
cr = Chr$(13) & Chr$(10)

'Sets the global variable to set the LDAP url
gstrLDAPURL = "pbmhmr.com:389"

'Sets the variables to check if the user has access to this database
ProjectName = DLookup("[Project Name]", "[System Data]")
UserName = GetUserName


If CheckAccess = True Then
Me.WelcomeLbl.Caption = "Welcome " & UserName & ", please enter your password."
Me.PassW.Visible = True
Me.PassW.SetFocus
Me.Label20.Visible = True
Me.Command21.Visible = True
Else
Me.WelcomeLbl.Caption = "I'm Sorry, you do not have access to this database."
End If

DoCmd.ShowToolbar "Form View", A_TOOLBAR_NO
DoEvents
End Sub
 
After review your description of your code it sounds like exactly what I have been looking for.

How would I implement your code? If you have a basic database that use that code or could explain how to use it correctly it would greatly appreciated.

I am using Access 2010 using VBA.

Thanks.
 
I will try to throw something together for you be Friday. Really an LDAP connection with the UserLog Table is all you need. The code will double check against LDAP for user group and permissions and then against the UserLog for Database permissions specific to that DB. For instance - I have a Supervisors group. In LDAP all supervisors belong to that group - but my HR director doesn't need the database my clinical supervisors need. Or - we have a DB that the HR Dir is an admin on but the clinical supervor has basic access. We control all of that with table login permissions.
 
That would be great. I appreciate the help.

Let me describe what I want to do so you can see if I may be going about this in the wrong way.

I am creating a new Access 2010 database with a split back end. I would like to use LDAP to have my user verified so only authorized users can access this database along with being password protected.

I understand some of the code. But I am wonder where I would place the first portion of code you wrote.

Also if using an Access file as the backend won't work and it would with and SQL server I am willing to make that change.

Thanks again.
 
I have attached everything you need to make the security work. The UserLog is hosted in a centrally located database and used for all database permissions but I included here. There is one place in the VBA to put your domain to establish the LDAP connection.
 

Attachments

Hi Texas Geek Girl,

Your files look very appealing. I have a database which I would like to assign read & write or read-only rights, based upon the user logged in into Windows. We're in an enterprise environment and it would be very interesting to get this to work. Can you help me out?

With kind regards,
Timo
 
Well - I have built in a couple of different tells - like in the userlog using administrator check, using different switchboards. The other way I set permissions is using Powershell Code in the Folder Sharing permissions area per user.

We host our DBs on a 2008 Win Server and for the most part have 4-5 different security levels built in using only DB (VBA) coding. We use User Groups for folder permissions unless there is an individual who needs to be locked down or opened up.
 

Users who are viewing this thread

Back
Top Bottom