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

Restrict access to a table when open button is clicked.

SteHawk85
P: 34
Hi

I have a database that requires a log in to gain access. Some people are 'Admin' others arenít. I have a hidden button when double clicked opens an Employees table. This table is used to check peopleís username and password when they log in, it also has information like Access Level and network username. what I need to happen is when this button is double clicked it runs a check against the employees table to see if the users access level is 'Admin', if it is they get access to said table and can update it so new users can be added allowing them to log in, is this possible? The code I am currently using to open the table is below.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Image67_DblClick(Cancel As Integer)
  2.  
  3. DoCmd.OpenTable "tblEmployees", acViewNormal
  4.  
  5. End Sub
  6.  
Thanks in advance

Ste
May 21 '12 #1

✓ answered by NeoPa

Before I go past this let me just advise that storing passwords in a table in plain text ensures your security system is a long way short of secure. This is never advisable.

Your explanation is not too clear to be fair, and what it implies contradicts the code shown, so I'll go with the code and assume you have an integer value available in the variable [lngMyEmpID] which can be used to determine the user record that you're interested in.

The code to get the string value for [strAccess] would be (It's really very simple after all that) :
Expand|Select|Wrap|Line Numbers
  1. DLookup(Expr:="[strAccess]", Domain:="[tblEmployees]", Criteria:="[lngEmpID]=" & lngMyEmpID)
Clearly, this would return a string value, but you'd need a line of code that does something with it.

Share this Question
Share on Google+
20 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Check out Function to Return UserName (NT Login) of Current User. With this you generally have no need for the user to log in separately.

Your table contains what level of access each user has, so look up in the table for the current user and determine their [AccessLevel] from their. Once you have the [AccessLevel] you can simply and easily run certain code only if the [AccessLevel] matches what you require for that code.
May 21 '12 #2

SteHawk85
P: 34
Hi

I think I am going to rip my hair out. I have had a go at using the Dlookup function on my piece of code however I'm in new water and don't really know if I am even barking up the right tree. I know where I am going wrong in the code I just don’t know how to right it. The 'If' function is the problem I think but again I could be totally wrong and all of it may be wrong.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Image67_DblClick(Cancel As Integer)
  2. Dim strAdminAccess As String
  3. If Me.Image67.OnDblClick = True Then
  4.  
  5. strAdminAccess = DLookup("strAccess", "tblEmployees", "[strAccess]= Admin")
  6.  
  7. DoCmd.OpenTable "tblEmployees", acViewNormal
  8.  
  9. End Sub
i know the '= True' is wrong but i dont know what else to put here.

Ste
May 21 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
Sorry to burst any bubbles Ste, but I see no signs here of the logic I suggested. Perhaps if you explained the data structure you're working with we could help some more. The first job is to get the user logon ID though. Without that the rest is going nowhere. You can't finish a race if you never go anywhere near the start-line ;-)

Don't rip out any hair, just take it one simple step at a time. If there's a step you can't manage then don't push on randomly (as you'll find yourself wandering in the desert), stop and report what you're stuck on.

First step - Provide information as to what data you're working with.
Second step - put Mary's code in from the linked article.
May 22 '12 #4

SteHawk85
P: 34
Ha! Sorry about yesterday I had 101 things on the go at the same time and I knew I was barking up the wrong tree! And I was certainly wandering lost in the desert. I will read over the link again (this time slowly) and then have another go. I have a bit less on my plate today and after reading I will try and explain what I need.

Thanks for your patience

Ste
May 22 '12 #5

SteHawk85
P: 34
Let me start again...

My database already has a 'Log in' system and that works great. It consists of a form which has a combo box containing a list of all users. After selecting the appropiate user they must then enter their password. After they enter the correct password they are given access to the database. As i say this works fine!

The problem I have is on my Switchboard (after they have gained access to the DB) I have a picture which when double clicked opens a table. This table (tblEmployees) is the one which the login form uses to lookup username and password. What I am trying to achieve is when a user double clicks this image in order to gain access, to add a new user to the log in system, I want a check to be run to ensure the user has 'Admin' rights (another piece of data that is held in the Employees table). This hasn’t been requested by the users i am just trying to add another level of security to the DB.

Hope this makes more sense then my rant yesterday

Ste
May 22 '12 #6

NeoPa
Expert Mod 15k+
P: 31,494
It makes sense, but doesn't take us forward at all. You still have not provided any information pertaining to what fields are in your record, and the reason I ask is that without something else in there it will not be possible. I'm assuming you have the requisite information stored if you're asking such a question, but I can't answer just on the assumption you might have something. I need details of exactly what is there.

Using a home-made system of logins rather than one already available to you and pre-security checked, is not going to change the fundamental concept much. You will simply use that ID instead of the one Windows would have returned. How you manage that within Your data structure though, we can only help with when we know something about it.
May 22 '12 #7

SteHawk85
P: 34
Hi Neo

The tblEmployees has the fields - lngEmpID, this is an autonumber ID that is generated when a new user is added, strEmpName, this is a Username e.g. mine is Ste, strEmpPassword, this is there password strAccess, this is the users level either 'Admin' or 'User' and finally fOSUsername, this is our departments username for the network i.e. ben jones would be jonesb.

hope this helps thanks again!

Ste
May 22 '12 #8

NeoPa
Expert Mod 15k+
P: 31,494
All good, and what does your login system return to you for use in looking up the correct record from the table?
May 22 '12 #9

SteHawk85
P: 34
Here's my code I have used for my login system:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private intLogonAttempts As Integer
  3.  
  4. Private Sub cmdLogin_Enter()
  5. 'Check to see if data is entered into the UserName combo box
  6.  
  7.     If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
  8.             MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  9.             Me.cboEmployee.SetFocus
  10.         Exit Sub
  11.     End If
  12.  
  13. 'Check to see if data is entered into the password box
  14.  
  15.     If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
  16.             MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  17.             Me.txtPassword.SetFocus
  18.         Exit Sub
  19.     End If
  20.  
  21. 'Check value of password in tblEmployees to see if this matches value chosen in combo box
  22.  
  23.     If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
  24.  
  25.         lngMyEmpID = Me.cboEmployee.Value
  26.  
  27. 'Close logon form and open splash screen
  28.  
  29.         DoCmd.Close acForm, "frmLogon", acSaveNo
  30.         DoCmd.OpenForm "F Start Up"
  31.  
  32.         Else
  33.         MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
  34.         Me.txtPassword.SetFocus
  35.     End If
  36.  
  37. 'If User Enters incorrect password 3 times database will shutdown
  38.  
  39.     intLogonAttempts = intLogonAttempts + 1
  40.     If intLogonAttempts > 3 Then
  41.         MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
  42.         Application.Quit
  43.     End If
  44.  
  45. End Sub
  46.  
  47. Private Sub Form_Load()
  48. On Error GoTo ErrorHandler
  49.  
  50. Me.Caption = SSH_APPLICATION_KMBC & " Log On"
  51.  
  52. CleanUpAndExit:
  53.     Exit Sub
  54.  
  55. ErrorHandler:
  56.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  57.     Resume CleanUpAndExit
  58. End Sub
  59.  
  60. Private Sub Form_Open(Cancel As Integer)
  61. 'On open set focus to combo box
  62. Me.cboEmployee.SetFocus
  63.  
  64. Dim i As Integer
  65. For i = 1 To CommandBars.Count
  66. CommandBars(i).Enabled = False
  67. Next i
  68.  
  69. End Sub
  70.  
  71. Private Sub cboEmployee_AfterUpdate()
  72. 'After selecting user name set focus to password field
  73. Me.txtPassword.SetFocus
  74. End Sub
  75.  
  76. Private Sub cmdLogin_Click()
  77.  
  78. 'Check to see if data is entered into the UserName combo box
  79.  
  80.     If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
  81.             MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  82.             Me.cboEmployee.SetFocus
  83.         Exit Sub
  84.     End If
  85.  
  86. 'Check to see if data is entered into the password box
  87.  
  88.     If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
  89.             MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  90.             Me.txtPassword.SetFocus
  91.         Exit Sub
  92.     End If
  93.  
  94. 'Check value of password in tblEmployees to see if this matches value chosen in combo box
  95.  
  96.     If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
  97.  
  98.         lngMyEmpID = Me.cboEmployee.Value
  99.  
  100. 'Close logon form and open splash screen
  101.  
  102.         DoCmd.Close acForm, "frmLogon", acSaveNo
  103.         DoCmd.OpenForm "F Start Up"
  104.  
  105.         Else
  106.         MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
  107.         Me.txtPassword.SetFocus
  108.     End If
  109.  
  110. 'If User Enters incorrect password 3 times database will shutdown
  111.  
  112.     intLogonAttempts = intLogonAttempts + 1
  113.     If intLogonAttempts > 3 Then
  114.         MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
  115.         Application.Quit
  116.     End If
  117.  
  118. End Sub
  119.  
  120.  
  121.  
When the correct Password is entered it takes you too the 'F Start Up' screen, which is a form showing Data Protection Policy and is timed for 30 secs then that closes and takes you to the CLASwitchboard.

The 'Admin' and 'User' statuses have exactly the same access, however this is why I want the tblEmployees to be protected from ~Users so only 'Admin' staff can add new staff members to the system.

ta

Ste
May 23 '12 #10

NeoPa
Expert Mod 15k+
P: 31,494
I have no idea why you might post this code. I can only imagine you have misunderstood what I thought was a simple question. Let me just point you to Before Posting (VBA or SQL) Code before leaving that with you and trying to rephrase the question so that we can continue sensibly.

What I need from you, as last details, is the name and type of the variable (or control) in which you store the value of the User ID that has already been security-checked (No matter how that checking is done), and which field in the table that this value should match?

I've understood the requirement for the question from the first post. The only thing holding back an apposite answer is the lack of relevant details so far available in the thread. You might want to make a mental note of how important it is to provide the relevant information for the question in the first place (as is instructed in those sticky threads posted to assist members to ask sensible questions). It would have saved you (not to mention others involved) a lot of time and effort on this occasion ;-)
May 23 '12 #11

SteHawk85
P: 34
Hi Neo

i'm sorry i am annoying myself with this at the minute so god knows how you feel.

i'm still a little confused (think my brain must be fried), the field in the table that the log in system checks against are the strEmpName - the user must select their username from a combo box then the strEmpPassword - this is a textbox, must match to gain access. this line of code shows how.

Expand|Select|Wrap|Line Numbers
  1. 'Check value of password in tblEmployees to see if this matches value chosen in combo box 
  2.  
  3.     If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then 
  4.  
  5.         lngMyEmpID = Me.cboEmployee.Value 
  6.  
once again sorry and hope this is what your after in terms of information
May 23 '12 #12

NeoPa
Expert Mod 15k+
P: 31,494
Before I go past this let me just advise that storing passwords in a table in plain text ensures your security system is a long way short of secure. This is never advisable.

Your explanation is not too clear to be fair, and what it implies contradicts the code shown, so I'll go with the code and assume you have an integer value available in the variable [lngMyEmpID] which can be used to determine the user record that you're interested in.

The code to get the string value for [strAccess] would be (It's really very simple after all that) :
Expand|Select|Wrap|Line Numbers
  1. DLookup(Expr:="[strAccess]", Domain:="[tblEmployees]", Criteria:="[lngEmpID]=" & lngMyEmpID)
Clearly, this would return a string value, but you'd need a line of code that does something with it.
May 23 '12 #13

SteHawk85
P: 34
Cheers mate!

Yes you are right the lngEmpID is an integer and the password's are stored as text however you can only see them as ********** in the text box. I didn’t actually set up the login system and it has just been 'borrowed' from another DB. Hence the confusion!

Thanks again for all your help! I will have a ponder and try the code
May 24 '12 #14

NeoPa
Expert Mod 15k+
P: 31,494
No worries on the advice. Pleased to help.

On the security point though, if you are under the impression that a 5-year-old couldn't view the data in the table without recourse to your form, then you might want to disabuse yourself of that particular impression. I appreciate that most office users don't have quite that level of intellect, and most won't even think about looking, but security is supposed to restrict access forcefully, rather than simply relying on the laziness / lack of awareness of any potential wrong-doer.

I also appreciate you're not looking for extra tasks to be lumbered with, but it's only right that I should bring this to your attention. At least then you understand what you're dealing with and can make informed choices on the matter.
May 24 '12 #15

SteHawk85
P: 34
To be honest I totally agree with you! I was just told to use the same log in system as used on previous DB's. Do you think I would be better off using the code you originally posted frm MMcCarthy?

The data that is being held is very sensitive, and I feel that a more robust level of security is important. With MMc code am I am able to select certain users to have access? The DB is held on a secure server that only certain users have access to, but there will be a lot of people who can see the DB who don't need to.

I would rather have the extra task and feel comfotable with what I am handing over then worry about somebody gaining access who shouldn't have any. Plus, since I didnt write the code I dont really know it or feel 100% comfortable with it.
May 24 '12 #16

NeoPa
Expert Mod 15k+
P: 31,494
SteHawk85:
Do you think I would be better off using the code you originally posted frm MMcCarthy?
I like easy questions. Yes.

It means, assuming you're happy with NT Domain Security, that you don't even need to maintain a password at all in your table and still have access to all other user-level info that's required.

If you do end up using a bespoke security system in Access, then at least obscure the password data in the table (as well as on the form of course). This typically means to encrypt it, and there are articles here to help with that (NB. The recommended approach is still to use Mary's suggestion and then passwords don't need to be stored in the database) :
  1. AES Encryption Algorithm for VBA and VBScript.
  2. RC4 Encryption Algorithm for VBA and VBScript.
  3. SHA2 Cryptographic Hash Algorithm for VBA and VBScript.
May 24 '12 #17

SteHawk85
P: 34
Hi Neo

The passwords are obsecured in the Table as *************, however I would still prefer to use NT domain security. Can you provide any information on it please? For example how it works and an example of how best to use it. Do I need a .dll to run the function?

Ta
May 24 '12 #18

NeoPa
Expert Mod 15k+
P: 31,494
SteHawk85:
The passwords are obsecured in the Table as *************
That's good. Now only 10-year-olds and older could crack it with a simple command such as ?DLookup("[Password]","[SecurityTable]","[AccountName]='Bob'") from the Immediate pane of the VBA IDE ;-D

SteHawk85:
Can you provide any information on it please?
You should find what you need in the linked article. If that isn't clear enough for you then I'll need an explanation of how far you did get and where you got stuck. You don't need any special DLLs to run it. Windows libraries are available as standard and the how of using them is included within the linked article.
May 24 '12 #19

SteHawk85
P: 34
Think this has taken me well outside my comfort zone and I don’t really know what to do with it. I have added the code as a function and saved it to the modules section of my DB. Now I am stuck as to what I am meant to do, I have tried creating a new form and asking for the username to be added and then when a button is pressed it calls the function. Nothing happens! Little bit stuck after reading the code and the comments there seems to be little in the way of advice as to what I am meant to do with this code after adding it as a function. Obviously there is a certain level of experience required to understand this and I am totally new to VBA.
May 24 '12 #20

NeoPa
Expert Mod 15k+
P: 31,494
OK. Let's start with the fact that the function will return a string value that reflects the logon ID that the user used to log on to the domain. My guess, from what you've posted already, is that this would probably match a field you referred to as [fOSUsername]. It would certainly be unique within the domain. The filter for such a record in your table would then be (assuming "JonesB" is the value returned from Mary's function) :
Expand|Select|Wrap|Line Numbers
  1. ([fOSUsername]='JonesB')
Thus, your code might look similar to :
Expand|Select|Wrap|Line Numbers
  1. Dim strUser As String, strWhere As String
  2.  
  3. strUser = sys_OrigUserID()
  4. strWhere = Replace("[fOSUsername]='%U'", "%U", strUser)
  5. DLookup(Expr:="[strAccess]", Domain:="[tblEmployees]", Criteria:=strWhere)
Sorry this took so long. I had it mostly done earlier (and thought I'd posted it), then I got an interruption and forgot all about it until now.
May 24 '12 #21

Post your reply

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