By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Password username help

P: 22
I have an Access 2010 database that I would like to give the Administrator, Manager, User or Viewer access to the database upon correct entry of their username and password on a form called Login. The username and password, as well as other related information is stored in a table call Staff. Pertinent fields of the Staff table are:
1. Employee_Num
2. Staff_Name
3. User_Name
4. Password
5. Status (1=Administrator 2=manager 3=user 4=viewer status)

Only an administrator shall be given access to the Staff table. I need to be able to add to each form the current username, status, date and time where records are added. Also, I need to add to any record that is edited, the username, status, date and time of the Administrator or Manager. How do I get the username from the Logon form on a record on another form like the patient information form? This is complicated by multiple users will be on different terminals at the same time. If you have a simple article to get started, I would appreciate it.
Thanks,
Carl23
May 9 '12 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,418
Carl23:
Also, I need to add to any record that is edited, the username, status, date and time of the Administrator or Manager.
I'm not sure how you'd do that as your design doesn't seem to support knowing who the Admin or Manager is.

Otherwise, you need to keep a note somewhere, either in a Front-End database if that's how you do things or maybe in a hidden control on a form that stays open throughout the session, of the PK of the [Staff] table. With that PK available across the whole project you're only ever a couple of lines of code away from all the data in the relevant [Staff] record.

NB. Never store passwords in clear text in a table. Always obfuscate them in some way first, preferably with some decent encryption (See AES Encryption Algorithm for VBA and VBScript, RC4 Encryption Algorithm for VBA and VBScript and/or SHA2 Cryptographic Hash Algorithm for VBA and VBScript).
May 9 '12 #2

P: 22
Hope this helps, the PK of the [Staff] table is the Employee_Num. Would it work to have the username accessible as a drop down combo box that also contains the PK?
Thanks,
Carl23
May 10 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
Carl, what's to stop them from using the shift key bypass to skip all the code and get direct access to the tables?
May 10 '12 #4

P: 22
I was on the Microsoft website at
http://bytes.com/topic/access/answer...-username-help

does their TIP solve this problem?
Tip To prevent users from bypassing startup options, disable the Bypass (SHIFT) key by using Visual Basic for Applications (VBA) code to set the AllowBypassKey property of the database. For more information about setting the AllowBypassKey property, click the link in the See Also section of this article.
Thanks,
Carl23
May 10 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
A few general pointers first based on my estimation on the scope of your project, most of these you probably (hopefully) allready have in your setup:
You must divide the application into a frontend application for each terminal and a backend database.
You must distribute a compiled version of the frontend.
You must disable the shift bypass key.
You MUST encrypt passwords.

Now onto the specific issue.

In AC2007 and AC2010 the Tempvars collection was added. You can use this to easily store the userID as well as the user role. Once the user has succesfully had his password verified you can store it like so:
Expand|Select|Wrap|Line Numbers
  1. tempvars.Add "UserID",2
where 2 should be the primary KEY of the person in your users table.
and recall it like so:
tempvars!UserID

You do the same for your Role (status you called it). Now when a form opens, you can set the properties accordingly, if you for example add this code to a custom module:
Expand|Select|Wrap|Line Numbers
  1. Public Sub SetViewMode(objForm As Form)
  2.    Select Case TempVars!Role
  3.       Case 1 'Adminstrator
  4.          objForm.AllowAdditions = True
  5.          objForm.AllowDeletions = True
  6.          objForm.AllowEdits = True
  7.       Case 2
  8.          objForm.AllowAdditions = True
  9.          objForm.AllowDeletions = True
  10.          objForm.AllowEdits = True
  11.       Case 3
  12.          objForm.AllowAdditions = True
  13.          objForm.AllowDeletions = False
  14.          objForm.AllowEdits = True
  15.       Case 4
  16.          objForm.AllowAdditions = False
  17.          objForm.AllowDeletions = False
  18.          objForm.AllowEdits = False
  19.       Case Else 'If all works well the case else will never execute. Its merely a failsafe
  20.          MsgBox "Unauthorized Access"
  21.             DoCmd.Quit
  22.    End Select
  23.  
  24. End Sub
and this code to your forms open event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.   SetviewMode Me
  3. End Sub
You can ofcourse also write the above values for each specific form if required. For instance a addition to the form you use to manage staff could look like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.   If tempVars!Role<>4 then
  3.     Msgbox "This form is for administrators only"
  4.     Cancel=True
  5.     Exit Sub
  6.   End If
  7.   SetviewMode Me
  8. End Sub



Finally on the matter of storing the edit details. For each table, you add the fields: ID_CreatedBy, dt_Created, ID_ChangedBy, dt_Created

In your forms BEFORE_Update event you then add:
Expand|Select|Wrap|Line Numbers
  1. If Me.NewRecord Then
  2.   Me.ID_CreatedBy=TempVars!UserID
  3.   Me.dt_Created=Now()
  4. Else
  5.   Me.ID_ChangedBy=TempVars!UserID
  6.   Me.dt_Created=Now()
  7. End If

That became a rather lengthy post. Bear in mind, that this is just examples of how it can be done, there are always several ways to do something.
May 10 '12 #6

NeoPa
Expert Mod 15k+
P: 31,418
If TempVars is session-level data then beware the likelihood of the session dying. This can happen for a number of reasons, but if it is held available regardless of the active session then that's good advice. It's already really easy to store data at the session level which is globally available so I cannot see why any intelligent person would create a concept of TempVars if they only gave the same features as what's already available.
May 10 '12 #7

Rabbit
Expert Mod 10K+
P: 12,357
You can disable the shift bypass, but the user, if they know what they're doing, can reenable it.
May 10 '12 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
Tempvars is session level data, that is stored even if a unhandled error occurs. I used a similar home-cooked solution for storing session level data that would be kept in memory, but had a backup placement in a table, in case a unhandled error found its way into my coding.
May 11 '12 #9

NeoPa
Expert Mod 15k+
P: 31,418
Smiley:
Tempvars is session level data, that is stored even if a unhandled error occurs.
That confuses me. Session level data is lost when the session dies (EG. when an unhandled error occurs and the operator selects "End".) If TempVars are maintained beyond this then I'm not sure they can be described as session-level. Either it's availability is limited to the live session, or it isn't.
May 11 '12 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im not sure how else to describe it. If you quit access the tempvars is reset, but a error will not reset it. So I would still use the term session level, unless you have a better word for it.
May 13 '12 #11

NeoPa
Expert Mod 15k+
P: 31,418
I don't. Nevertheless, the last thisng it would be is session-level, as that indicates exactly what it isn't. I assume when you say about errors resetting things you're talking about when the operator chooses to reset rather than any time an error occurs. Believe me Smiley, I'm not trying to criticise your English. It's worlds beyond my Danish. I'm simply trying to ensure that what is here is clear and precise so that all can understand what is said and the implications.

It does sound, assuming I have understood you correctly (and I think I have now), that this is indeed something which fills an important gap. I wasn't aware of these (obviously from the conversation), but if I had been, I think I would have suggested this approach too. Better than storing values on forms or writing defensive code that repeatedly has to check for the availability of the data before ever using it.
May 14 '12 #12

TheSmileyCoder
Expert Mod 100+
P: 2,321
If you have a module with a global (I.e. defined in the general area at the top, and not inside a procedure) variable, it will reset if:
A unhandled error occurs
Someone enters into VBE and hits the reset button

Tempvars are kept in both of the above cases.
May 14 '12 #13

NeoPa
Expert Mod 15k+
P: 31,418
Smiley:
...it will reset if:
A unhandled error occurs
You probably got that from MS somewhere. It's not quite correct. It only resets if the operator chooses "End" if they are prompted. If "Debug" is chosen the session continues regardless of the unhandled error.

That said, your meaning is very clear and now understood. As I said earlier, I'm convinced it's the way to go for this, as long as v2007 or later is being used.
May 14 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.