Restricting access to database using forms

[vandy]

Hi All,

I have created a database in access2003 and given selective restrictive
access to users using the security wizard.

I have a user who is not on the network and thus not able to access the
workgoup and secruity mdw files which i have created.

I have created a user login form which checks for user name and password.
Private Sub btnlogin_Click()

If IsNull(Me.quserid) Then
MsgBox ("You Must Enter a Valid User ID !!")
Exit Sub

Else

If IsNull(Me.qpwd) Then
MsgBox ("You Must Enter a Password!!")
Exit Sub

End If
End If

If Me.qpwd.Value = DLookup("PWD", "Authorized", "UserID ='" &
Me.quserid.Value & "'") Then

DoCmd.OpenForm "MainMenu", acNormal
Else
MsgBox ("Invalid Password!Try Again!")
End If
End Sub


Question: How to modify this to enable read only acess to all forms and
screens to a selective user x who is not on the windows network.

Right now i want the user to login in using the login form and when he types
in his username and pwd all the screens and forms should be read only.

can this be done. Thanks in advance.

 

[Carl Rapson]

Hi All,

I have created a database in access2003 and given selective restrictive
access to users using the security wizard.

I have a user who is not on the network and thus not able to access the
workgoup and secruity mdw files which i have created.

I have created a user login form which checks for user name and password.
Private Sub btnlogin_Click()

If IsNull(Me.quserid) Then
MsgBox ("You Must Enter a Valid User ID !!")
Exit Sub

Else

If IsNull(Me.qpwd) Then
MsgBox ("You Must Enter a Password!!")
Exit Sub

End If
End If

If Me.qpwd.Value = DLookup("PWD", "Authorized", "UserID ='" &
Me.quserid.Value & "'") Then

DoCmd.OpenForm "MainMenu", acNormal
Else
MsgBox ("Invalid Password!Try Again!")
End If
End Sub


Question: How to modify this to enable read only acess to all forms and
screens to a selective user x who is not on the windows network.

Right now i want the user to login in using the login form and when he
types
in his username and pwd all the screens and forms should be read only.

can this be done. Thanks in advance.

It can be done, but it will require some VBA coding. You will first need to
add a field to your Authorized table to indicate whether the user is
read-only. Fetch that value, and then pass something in the OpenArgs
parameter of OpenForm to tell the form to make itself read-only. In the
form's Open event, check the contents of OpenArgs and if it's appropriate,
you can either set the form's Allow Additions, Allow Deletions, and Allow
Edits properties to No (essentially rendering the entire form read-only), or
you can loop through the form's controls (using the Controls collection) and
set each control's Locked property to Yes. I usually do the latter, because
it gives me more granular control over the form; I can leave some controls
unlocked, for example (like a combo box for selecting a record to view).

Look in Access help for the Controls collection for examples of how to
iterate through it and how to use the ControlType property to tell what kind
of control it is. You can also search these newsgroups for more information
on OpenArgs, the Controls collection, and ControlType property.

Carl Rapson

 

[vandy]

Hello Carl,

Thanks for your reply. Is there any direct way through this. Is it possible
to compare for values of the username and his password and if it matches than
allow read only access to all forms or in my case only give access to 2 forms
which queries values from tables which the user needs to access.

Can this be done with some vba coding. Also as mentioned the access security
wizard already created user groups like read only users, full permission
users etc. so is it required to add a feild in my authorization table to make
this work.

would appreciate your comments on the above.

 

[Carl Rapson]

I can't really help you much with Access' built-in security, as I never use
it. There are others here who have experience with it; perhaps one of them
will jump in with some advice.

I'm not really certain what you're asking in your first question. The way I
do security is, I have a table defining the different security "roles", and
another table linking those roles to various program functionality - menus,
forms, controls within forms, reports, etc. Then, in the Users table, I
assign a role to each user. When the user "logs in", that role is fetched
and used from then on to control what the user sees and does. For example,
when each form opens, I check the user's role and (for example) hide certain
controls and lock others. On my menu forms I control which menu options the
user has access to.

Other will tell you that this type of security is not really very "secure",
and they're right. But at my company it's not really so much an issue of
security as it is an issue of control. A determined user could easily open
the back-end file and modify his role or even damage the data, but the
chance of that happening is very remote and everything is backed up
regularly so there wouldn't be much loss. I'm more interested in controlling
the user interface and presenting things to the users in the most efficient
manner. While it may be possible to use Access' built-in security to have
the level of control I've described, I could never figure out how to do it.
It's always been easier for me to just do it myself than to try to work
within Access' security framework.

Carl Rapson

 

[vandy]

Hi Carl,

Thanks for your help. In my case i have always been using Access Security
and made it work and have been away from coding. In this case there was a
user who was not logged into the network and so i had to have restrictive
access for him.

Once again thanks for your patience i am going to attempt to see if i can
device my own security since just like your company user control takes
priority over security. So yes i probably have to learn how to program using
my own security tables.

thanks