Retaining a user ID access right throughout a database 1

[Powerhouse123]

Hi,

I have a database which I've recently converted to SQL backend tables.

Since the conversion when I use my log-in form which takes a network login and password, the access rights are no longer consistent throughout the database.

When I get to the main form, for read only users they can edit certain subforms but it seems to be completely random each time I log-in.

Does anyone have any tips on how I can retain the userid throughout the database because it's really frustrating. In the local version I didn't have any issues, it remembered and displayed the right things according to the user's access right.

Many thanks in advance!

Powerhouse

 

[jsteph]

How were you implementing security before? Were you using JET security or a homegrown code-level security?

And keep in mind that ODBC connections can store passwords, etc, so in front-ends is a good idea to have code that completely re-links all odbc tables each time it's opened, for this and other reasons.
--Jim

 

[Powerhouse123]

Being a real novice at this part of Access I'd say "homegrown code-level security". I was just chuffed it all worked when it was a local table.

This is the code on my login button

Private Sub cmdLogin_Click()
On Error GoTo Err_cmdLogin_Click
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblSecurity", dbOpenDynaset, dbSeeChanges)

If Not IsNull(Me.txtUserID) And Not IsNull(Me.txtPassword) Then
rst.FindFirst "Password = '" & Me.txtPassword & "'" & " And UserID = '" & Me.txtUserID & "' And Active"

If rst.NoMatch Then
MsgBox "You may have entered the wrong User Name or Password." & Chr(13) & _
"Please enter the correct User Name and Password or " & Chr(13) & _
"contact the Database Adminstrator for assistance " & Chr(13) & _
"as your user account may not be active.", vbOKOnly + vbCritical, "Logon Denied"
Me.txtPassword.SetFocus

ElseIf Me.txtPassword = "password" Then
MsgBox "This is the first time using the database or your password has been reset." & Chr(13) & _
"You must change your password before you can enter the database.", _
vbOKOnly + vbExclamation, "Change Password"
stDocName = "frmUserLoginNew"
stLinkCriteria = "[UserID]=" & "'" & Me![txtUserID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Else
stDocName = "frmStartup"
stLinkCriteria = "[UserID]=" & "'" & Me![txtUserID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmLogin", acSaveNo
End If

Else
MsgBox "You left the User Name and/or Password blank." & Chr(13) & _
"Please enter the correct User Name and Password or " & Chr(13) & _
"Contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
Me.txtPassword.SetFocus
End If

With User
.AccessID = rst.Fields("AccessID")
' .ViewID = rst.Fields("ViewID")
.Active = rst.Fields("Active")
.Password = rst.Fields("Password")
.SecurityID = rst.Fields("SecurityID")
.UserID = rst.Fields("UserID")
End With

rst.Close

Exit_cmdLogin_Click:
Exit Sub

Err_cmdLogin_Click:
MsgBox Err.Description
Resume Exit_cmdLogin_Click
End Sub

and then I've got a module full of access rights for each form, basically hiding different buttons for different users and making some forms non editable etc.

I use the below code on the specific OnLoad event of the corresponding form:

AccessRights = 7
mdl_AccessRights.SettingAccessRights

This feels homegrown to me!

Are you saying where I've named a table in any of my code (login form and anywhere else within the database) I need to write the preceeding target ODBC link first?

Apologies if I've misinterpretted what you were saying.

I'm out my depth with this login function other than what I've explained so would appreciate any simple help you can offer.

Many thanks!

 

[jontout]

Are the UID & PWD stored on the SQL backend or still in Access? If not, I'd keep them on Access
Have you checked that the permissions on the SQL box are correct?

 

[MajP]

Your login is not the problem. Really need to see the code for
mdl_AccessRights.SettingAccessRights

Also what is "User"?
These are not the properties of a
With User
.AccessID = rst.Fields("AccessID")
' .ViewID = rst.Fields("ViewID")
.Active = rst.Fields("Active")
.Password = rst.Fields("Password")
.SecurityID = rst.Fields("SecurityID")
.UserID = rst.Fields("UserID")
End With

Since I do not see it declared anywhere it is global in scope. That is not the properties of a DAO user object, and I do not think an ADODB user object. So I am guessing it is a custom class that is going out of scope. Do you have a User class defined?

 

[Powerhouse123]

Thanks to everyone so far.

MajP... Here is some mdl_AccessRights code:

Public Sub SettingAccessRights()

Select Case AccessRights

Case 1: AccessRights = 1
If User.AccessID = 1 Then

Form_frmSearch.cmdAdmin.Visible = True
Form_frmSearch.lblAdmin.Visible = True

Form_frmSearch.cmdTraining.Visible = True
Form_frmSearch.lblTraining.Visible = True

Else

Form_frmSearch.cmdAdmin.Visible = False
Form_frmSearch.lblAdmin.Visible = False

Form_frmSearch.cmdTraining.Visible = False
Form_frmSearch.lblTraining.Visible = False

End If

Case 2: AccessRights = 2
If User.AccessID = 1 Then

Form_frmChildDetails.AllowEdits = True
Form_frmChildDetails.AllowDeletions = True
Form_frmChildDetails.AllowAdditions = True
Form_frmChildDetails.cmdDelete.Visible = True

Else

Form_frmChildDetails.AllowEdits = False
Form_frmChildDetails.AllowDeletions = False
Form_frmChildDetails.AllowAdditions = False
Form_frmChildDetails.cmdDelete.Visible = False

End If

'this goes on for a few forms in a similar vein
End Select
End Sub

In regards to the With User comments, to be honest I've got no idea what it does, I took the whole code from a database which works perfectly using the same coding?

Do you have any suggestions as to what I should do to amend it? I understand what you are saying in that it needs to b defined but I'm not sure how? I assume User means...user ID? Is it necessary?

Many thanks...

 

[MajP]

I am going to put my money on this guess, based on what I am seeing.

The original developer uses a lot of global variables. Global variables are variables defined outside of sub routines at the top of standard modules. Something like

Option Explicit
Option Compare Database

Public someGlobalVariable as long
Public someOtherGlobalVariable as string
....

Local variables are defined at the top of procedures

Public sub someSubroutine()
dim someLocalVariable as variant
dim someOtherLocalVariable as string
end sub


global variables should persist through the life of the application. Local variables persists until the sub routine end.

You should use global variables carefully, because you have a lot less control/visibility of when values are set.

My guess here is where the problem is. The developer uses a global variable for AccessRight and "User". In my mind this is very poor programming. Are these values set? If you throw any error in your application these value go to 0. Has some other routine set this value to 0 or something else.

Public Sub SettingAccessRights()
'No variable defined thus has to be a global variable.  
Select Case AccessRight
'and it then falls out of the select case giving you the
'"else" properties

now you can test this very easily if this the problem

Public Sub SettingAccessRights()
if User.AccessID <> 1 or (AccessRights <> 1 And AccessRights <> 2) then
Msgbox "Access Rights Lost. User.Access = " & User.AccessID & " AccessRights = " & AccessRights
end if


This will pop up a message to show the value of Access rights, and User.accessID if they are lost.

Bottom line I believe your problems result from poor coding and excessive use of global variables.

If I was fixing it my fix would be to have functions that get the UserID and AccessRights.

Public Sub SettingAccessRights() 
  dim intAccessRights as integer
  dim intAccessID as integer
  intAccessRights = getAccessRights()
  intAccessID = getAccessID()

  select Case  intAccessRights
.....
end sub

public function getAccessRights() as integer
  some code here to pull the value from some location. Not sure how this is done would have to see more code
end function

Public function getAccessID() as integer
  'I would have saved all the "USER" values to a local table
  'I would use code to read the AccessID from the local 'table
end function

Can you find a class module called "User"?

 

[Powerhouse123]

Wow! Thanks so much... A lot to digest..

Here is the basUserInfo module

Option Compare Database
Option Explicit
Public Type UserInfo
' ViewID As Integer
AccessID As Integer
Active As Boolean
Password As String
UserID As String
SecurityID As String
End Type

Public User As UserInfo

Inserting that ifUser...code as per your suggestion it pops up all the time telling me the correct userid and accessright ID for the form it is loading...is that bad?

So going forward...

1) I update my SettingAccessRights code, defining things as per your code above before the select case

2) Need to work out what code to put in the public function getAccessRights() as integer section

3)Need to work out what code to put in the Public function getAccessID() as integer

Thanks so much...sorry to be so ignorant!!

 

[MajP]

it pops up all the time telling me the correct userid and accessright ID for the form it is loading...is that bad?

Not bad, but depends what it indicates. You will need to test it until you hit the case where the access rights are not consistent.

When I get to the main form, for read only users they can edit certain subforms but it seems to be completely random each time I log-in
[/qote]
When you have an instance where the rights are inconsistent what are the values of
AccessRights and User.AccessID ?

If they are correct values but the form is giving incorrect permissions, then the problem is somewhere else.
If the values are incorrect then you have narrowed down the problem.

You may just want to get rid of the message box and print to the immediate window. Then when you see the instance occur read the immediate window.

if User.AccessID <> 1 or (AccessRights <> 1 And AccessRights <> 2) then
'Msgbox "Access Rights Lost. User.Access = " & User.AccessID & " AccessRights = " & AccessRights
debug.print now() & " User AccessID " & User.acessID & " AccessRights = " & AccessRights
end if


So my proposed fixes are based on the assumption that you will see when the Access Rights become incorrect it has to do with the values of AccessID and AccessRights not being correct.

"User" is a global variable. This is a bad name since it is a reserved word used by VB. When you use a variable that VB already uses (i.e User of the DAO object) you can run into troubles especially if it is global.


If it was me fixing this, I would build a table in the local database (Assuming each user has their own front end)

tblUser
ViewID
AccessID
Active
Password
UserID
SecurityID

This is a table with a single field.
1) in your login instead of saving these values to a global "User" variable then simply use a insert query to write to the table.
2)Ensure on close of the database you delete from the table

then you can simply have functions that return the value instead of any global variables.


PubliC function getAccessID() as integer
getAccessID = dlookup("AccessID","tblUser")
end function

then on your forms
public sub someProcedure()
dim intAccessID as integer
intAccessID = getAccessID()
if intAccessID = 1....


This concept works fine assuming you have set your database up correctly for multiple users. Please tell me that each user has a copy of the front end on their local machine linking to the back end tables. If you say you are all sharing a front end on the network, you have bigger problems.


Now using global variables is not always bad, but you have to be careful and understand the implications.

 

[Powerhouse123]

Hi and many thanks for your continued support...

1) I'd love to tell you that each user has a copy of the front end but I've got bigger problems as all are sharing a front end on the network!!

2) I've tried to detect where things are going wrong so here's what I've done...

I've stripped out all the select case coding which is unnecessary so essentially we've now got two user levels 1) Admin - Full control and 2) User - read only (controlled by allow edits = false and not making admin buttons visible etc)

When I log in for both user profiles I see what I expect which is great and I can navigate around quite happily without losing any access rights.

The problem comes with my main form which represents a child and has several subforms attached to it one for address, parent info and an assessment log which has a history log attached to each assessment, all on the main form

This all works fine BUT... I've setup using tek tips previous support that when an assessment is added, two default records a inserted into the assessment history table to save the user having to select these required records each time. This was working fine with the local table version but I noticed that SQL tables don't actually insert a record into a table until a record is saved (ie create an auto ID number untli the record is saved). It is the auto number which I require in order to run this routine successfully.

In order for the insert into function to work correctly and mean the records are associated to the correct assessment, it needs to pick up the newly created assessment id number and place that in the assessment ID field within the history table.

For some reason this will only work if the child form is an established record itself otherwise it causes an error (ie if you create a new child, it requires you to close down and reopen the form before creating an assessment)

As a work around... after the child's name is entered on the after insert event I save the record to allow me to see the newly created childID and then store that, close the form and reopen it using the stored childID. Works fine in local table version but this is where I lose the user access rights in the SQL table version.

I inserted that ifUser code and checked the access rights when I open the child form and again when the close and reopen code kicks in - they were the same which I guess is no real help because the error msg shows when the accessid is lost?, it says Access = 1 AccessRights = 4 ... for all the appropriate subforms, if I don't actually create a new child all my accessrights work fine so it's something to do with this form and the adding a new child process...

Are there any work around fixes you could offer as I don't feel confident enough to start again as per your suggestions unless you have enough time to kindly right idiot proof guides (I mean you've done well with me so far!) and I'm so close to getting a working solution...

Thanks so much...

 

[MajP]

1) I'd love to tell you that each user has a copy of the front end but I've got bigger problems as all are sharing a front end on the network!!

Once you get the database working you really need to fix this. Multi user Access databases are not meant to be deployed this way and can lead to corruption of the database and poor performance.

2)
For some reason this will only work if the child form is an established record itself otherwise it causes an error (ie if you create a new child, it requires you to close down and reopen the form before creating an assessment)

Is this error trapped? If this is untrapped then all of your global variables go out of scope.

I inserted that ifUser code and checked the access rights when I open the child form and again when the close and reopen code kicks in - they were the same which I guess is no real help because the error msg shows when the accessid is lost?, it says Access = 1 AccessRights = 4 ... for all the appropriate subforms, if I don't actually create a new child all my accessrights work fine so it's something to do with this form and the adding a new child process...

Need you to be very clear here. When the access rights stop working:,
what are the values for Access and AccessRights?
What are the values that you would expect?

You say the AccessRights are = 4, but your code only sets permission for 1 and 2. If 4 is supposed to have some kind of permission what are they for:
Form_frmChildDetails.AllowEdits =
Form_frmChildDetails.AllowDeletions =
Form_frmChildDetails.AllowAdditions =
Form_frmChildDetails.cmdDelete.Visible =

If 1 and 4 are the expected values then my assumption that they are going out of scope is false. If they are bad values it indicates the problem is somewhere else, and now can start looking there.

Can you provide the code where AccessRights are set. AccessRights is a global variable and need to see the code that includes something like
AccessRights = ....

 

[Powerhouse123]

First question, how do you trap an error?

In regards to the where is the AccessRights = bit...

It sits in mdl_AccessRights as detailed above with all the select cases...

Case 1: AccessRights = 1
If User.AccessID = 1 Then

Form_frmChildDetails.Allow edits = True
...
Else

...

Case 2: AccessRights = 2
If User.AccessID = 1 Then

...

Each Case represents a different form and on the on Load event of each form I've put...

AccessRights = 7 (whatever corresponds to the select case no.)
mdl_AccessRights.SettingAccessRights

"Need you to be very clear here. When the access rights stop working:,
what are the values for Access and AccessRights?
What are the values that you would expect?"

So, when I open the main Child form normally, I get each subform loading and telling me the right AccessId = 1 and the AccessRight = 1,2,3... according to the apporpriate case for the subforms loading.

When I then create a new child the closing and opening function runs after updating the name field and the form loads again, the little error message as above occurs in the same order and doesn't appear to be different BUT whenthe form does load it has lost some of its rights as what weas eitible before becomes read only.

When I exit the form and load it again it says AccessID = 0, AccessRight = 1,2,3...

Does this help?

Thank you so much for your time...

 

[MajP]

No the SettingAccessRights() is checking the value of AccessRights and based on the value it sets form permissions. It is not assigning the Value to AccessRights. However, it appears to me to be incorrectly written.

Case 1: AccessRights = 1

Makes no sense. It says if AccessRights = 1 then let AccessRights = 1

Should look like

select Case AccessRights
Case 1
'code if accessRights = 1
Case 2
'Code if accessRights = 2
Case 3
...
Case Else
' Code to do if it does not meet any other cases
end select


So see if there is a public variable called accessrights.


A trapped error is done using error trapping within the procedure something like

Public sub someProcedure()
On Error goto lableError
code here

exit sub
lableError:
Msgbox err.number & " " & err.description
resume next
end sub

This handles the error and pops open a message. Without the error handler my code fails and I get a VB error message with. If that happens all global variables valures are lost. So I think that may still be the problem. You throw an untrapped error and then AccessID = 0. I am guessing once AccessID goes to zero you lose your form permissions.

So your first band aid is to trap the error. This is not a permanent fix, but it will answer if that is the cause.

 

[MajP]

http://www.vb6.us/tutorials/error-handling

 

[Powerhouse123]

I think I've found the Public variable at the top of md_AccessRights...

Option Compare Database
Option Explicit
Public AccessRights As Integer

You said the below code doesn't make sense but it seems to work until we get that untrapped issue...??

Public Sub SettingAccessRights()
Select Case AccessRights
Case 1: AccessRights = 1
If User.AccessID = 1 Then
etc

Unless you are saying, I'm just lucky I've got it working and it's not good programming!

So I tried putting some error trapping code you suggested around the...

Public Sub SettingAccessRights()
On Error GoTo lableError

Code
End If
Exit sub
lableError:
MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub

And we getting almost perfect results... the two subforms (Parents and Addresses) seem to retain the appropriate access rights when I create a new child record but the assess,emt history subform which is associated with each assessment doesn't hold it? Is it somethjing to do with being one step further removed from the main form? ie Main form (child details), subform (Assessment) and then sub-subform (assessment history) so it needs one more layer of code somewhere to help retain the user id during that procedure?

Silly thing is if you close down the main form and open it again manually (form close) it all works fine!!

Thanks as always!

 

[MajP]

I'm just lucky I've got it working and it's not good programming

1) The code is not really incorrect, but a little confusing and part of it does nothing. The first part says in sense

If AccessRights = 1 then let AccessRights = 1

So that does nothing, and it suggests to me the developer may have gotten lucky and was really trying to do something else. Second although you can use : in vb programming, it is rarely ever used and signifies two lines of code on the same line. Just adds a layer of confusion to what the developer's true intent was. So although this works
Case 1: AccessRights = 1
it does not make a lot of sense and should simply be
Case 1
Unless the developer was trying to do something else

2) Sorry to say this, but I think this whole design is poor programming. The developer uses global variables excessively, without control over them. It is just not bullet proof.
So again with this design if you ever throw an untrapped error your program will then give unexpected results because all of your global variables are lost.
At the absolute minimum to put a band-aid on this design ensure EVERY ONE of your your procedures have error handling. Even the simplest procedures

Silly thing is if you close down the main form and open it again manually (form close) it all works fine!

So without being able to actually see this, I am working on the assumption that it has to do with the loss of global variable values. If you never throw an error and this problem still occurs then likely there is something else going on. But my guess is a global variable values is lost, but when you open the main form it resets the values.

Read up on vb debugging. Messages boxes and debug.print can show you what is occuring in your code and the value of your variables. But vb has more features to help step through the code.
If you can isolate the value of your variables after a problem occurs and then again after you reopen the main form, that may help a lot.

 

[Powerhouse123]

Thank you so much for your time, advice and patience..it has been very much appreciated.

I believe that I have managed to create my very own plaster for my database problem which at the moment works!

I've simply repeated the close and open form code after inserting the name of a child and this seems to reset things. Albeit taking a fraction longer to run, I think it's worth it!

I'm sorry for anyone reading this thread in the hope of learning how good programming techniques but to be honest I'm just glad I've got something working at last.

Thanks so much MajP...you've been a true servant

Until it falls over again...best wishes!

 

[MajP]

I still would strongly recommend
1) Add lots of error trapping to your procedures
2) Provide each user a local copy of the front end to run on their own computer. Their front end should have the code, forms, reports, queries, and macros. It should link to the tables in your backend.