username and passwords for acces 2010

[johnlatona123]

hi!

a little background...i am a novice access user, learning as i go, so bear with me

this may all sounds like a bit of crazy talk, but i could really use a hand in writing something that accomplishes the following in microsoft access 2010:

i want to create a username and password function that restricts access to various forms in my database through general levels of permissions.

this would need to be something i can hide from other users. how do i do that?

i need to have acess to the area where i can update users names and passwords.

my first thought was to create a table containing all users names and passwords and use that as a means to open other forms via a "login form" but that has not panned oout like i hoped.

and i would like to tie this entire concept of being "logged in" with a timestamping funciton i have put together.

the timestamping function looks somthing like this and is contained in each form in the database and is functional on all the forms in my database:

Private Sub Form_AfterUpdate()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
'
strSQL = "SELECT * FROM [Material Tracking]"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
With rst
rst.AddNew
![job changed 2] = [Job]
![time changed 2] = Now()
![part number changed] = [part number]
![quantity changed 2] = [quantity needed]
![dock date changed] = [dock date]
![PO changed] = [PO number]
![supplier changed] = [supplier]
![buyer changed] = [Buyer Name]
![part notes changed] = [part notes]
![when backorder filled] = [Backorder filled]
rst.Update
End With
'
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub

any help in this regard would be much appreciated.

thanks!

john L

 

[Trevor G]

John there is sample database for Passwords etc in the form, look at the sample database side.

As a pointer think about this.

Each Person logs into the PC and you can use Environ("Username") to pick up the name of the PC, so you could have that in a table, then add a couple of textfields on a log in form and keep them invisible and from a drop down select the table and use 2 fields, the Environ Field (Usernames) and password, if they users then select the correct name and enters the password correctly then use a DLookup to poin them to the form you want to give them access.

 

[johnlatona123]

thats a great idea trevor, any idea how i can write the code to do such a thing?

also, i guess i should mention i am dealing with about 300 PC's on the LAN that all would have access to this database and they all would need to be identified seperately. though in some cases there are multiple users of certain PC's which leaves me with my initial problem of not knowing who is doing what.

 

[Trevor G]

John take a look at the attached.

I have taken some elements from a Database so you can see what happens.

When the database opens it activates the login form (simple autoexec macro)

The drop down is listing the staff from the tblStaff (use F11 to access the database window) and then you will see the staff table, add your details to it and test it out.

If you look on the login form in design view you will find the 2 fields and on the command button you will find the code, also there is a command button to send email for access request, it is set for Outlook 2007

 

[johnlatona123]

wow thats pretty slick, im gonna play with it and see if i can make it do what i need it to do

i may have more questions

thanks again!

 

[Trevor G]

Your welcome John,

Will keep an eye out for your questions as and when.

 

[johnlatona123]

Private Sub cboStaff_AfterUpdate()
Me.txtUser.Value = Me.cboStaff.Column(2)
Me.txtEnviro.Value = Environ("username")

what does the Environ("username") reference?

im trying to mod this thing to suite my purposes.

can i swap something like the pc number for a password?

i think with numeric access values i can write a macro into all of my forms that will check for x level of access to be able to open a form...

 

[Trevor G]

Here is some code which will give you the list of things you can use Environ with.

I typically use 50 for the loop because I've always had less than that, but you could certainly increase it if needed. This code will print out the name and value of the environment variables on a given machine.

Don't forget to display the immediate window.

Sub test()
Dim i As Integer
Dim stEnviron As String
For i = 1 To 50
' get the environment variable
stEnviron = Environ(i)
' see if there is a variable set
If Len(stEnviron) > 0 Then
Debug.Print i, Environ(i)
Else
Exit For
End If
Next
End Sub

If you adjust to use the password make sure you adjust the Combo and textbox data as well, so it checks the correct column.

 

[johnlatona123]

i appreciate all the help trevor. but could you possibly dumb that down a bit for me? i am not sure i quite understand the concept here.

sorry!

 

[Trevor G]

OK in the staff table change the filed information to passwords and add the passwords you want people to use, then you need to check the combo box so when the user selects there name it checks that the password has been added in correctly.

A point here you will find plenty of users who will be really fed up with having to enter so many passwords, ie password to log on to PC and then into Database, so the Environ takes care of that as it checks the PC name against the UserName that has been selected.

 

[johnlatona123]

hey trevor! after having played with your login screen, i cant quite get it to do what i want it to do. im just not that adept at VBA.

do you have a login program setup to work with a username and password rather than the eviron function? some users will use multiple PC's. for my purposes it doesnt really matter what PC someone is using.

ideally i would like to have something like a table that contains 3 things:
username, password, and access levels in numeric value

so i set user a's access level to a 3 and user b's access level to a 4...

i want it to work out so when either user logs in from the same PC they are directed to a specific form based on thier personal access level.

i would like to tie in an "active user" function into this so that i can associate it with my initial posts timestamping function.

does that make sense? i hope you can help me out here, i kinda suck at this

on a somewhat related note, does anyone know how to "lock" or indefinately hide all access objects from other users ? i want to be the only one who can work in the background and i want all my users to work through my form interfaces. thoughts?

thanks again

 

[Trevor G]

Why not create your username field, and password field then use a combo on the form to allow a selection from the username and add a textbox to ask them to put in the password, then just do the same as I did to see that they match in 2 additional textboxes that are hidden.

You don't have to use the Environ options although there are some useful features there.

As far as protecting your back end, once you have created all the elements of your database you can split it so the tables are in one part and the forms are in another database. BUT DON'T DO WITHOUT COPYING THE WHOLE DATABASE FIRST AND TEST IT.

You can also then hide the Navigation Pane etc.

 

[johnlatona123]

im still failing miserably with this. can anyone help me write/design it?

 

[Trevor G]

John

I have created a real basic database with a logon form and then main form, there is a staff table which fills the combo box, you select a name and then it will fill the check options. I have left everything visible so you can see it happen.

When you click the command button it will check the password against the password in the combo box column if it meats it will give you access otherwise it will give you a message.

I hope you can then expand on this. Press F11 to see the Table.

 

[Trevor G]

im still failing miserably with this. can anyone help me write/design it?

How have you got on with this issue?

 

[johnlatona123]

thanks for following up on this. i have successfully created username and password protection for my database.

simple description of my solution:

form with username and password entry areas and code behind some buttons to get you logged in based on "access levels"

changed accdb to accdr

created a front end database that contains the username and password table, this DB is encryted.

thanks ot everyone who helped me out with this one

in addition to this, i have set a global variable for the username to match up with my "change tracking" function so that my db writes records stating who exactly changed what and when.

 

[hortizjr59]

Hello:

I reviewed this post and found your sample database to be very useful for me. My questions is:

I am using Access 2007 and I notice when I open your application you have Users and Permissions + Replication Options (Administer) on your Database Tools ribbon. I have been trying to findout how to customize my ribbon the same way with no luck.

Nay help would be appreicated.

Thanks:

 

[Trevor G]

You have to have the Database saved as an earlier version ie MDB rather than ACCDB then you can use the Security Wizard which can be found by customising the Quick Access Toolbar.

Microsoft removed this function for both version 2007 and 2010.

 

[hortizjr59]

Got it to work.

Thanks for your help.

One other question; I used your code with some minor changes however since I am a bit of a novice at this my question concerns the tblestaffAccess. If I am logged in on my computer and select my name from the dropdown it opens the form I selected, if I chose another name I get the please contact to gain access pop up. Does this occur because I have to chose only my name.

Thanks again for your help.....

 

[Trevor G]

If you are using the Database sample which only asks you click drop down then it is using what is called the Environ command, this is checking the name of the PC, so even though you have selected the persons name, its not there PC. So it wont let you in.

The other version wants you to select your name and then add a password so that isn't PC specific.