-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
User restrictions
- Thread starter lulugreg
- Start date
Yes, it is possible.
You need to look into Tools/Security. Under that, you would set up a Workgroup and then identify the users and groups allowed to use that particular db. You also need to set permissions. This is where you would define RO and RW abilities.
I don't have any books on MS Access Security to point you to, but you have to learn up on that in order to accomplish what you want to do.
I guess you code write VBA code also, but if you're not using the Security feature, Access defaults to all users accessing a db as Admin and without some foolproof way of doing it, writing code would have it's drawbacks.
You need to look into Tools/Security. Under that, you would set up a Workgroup and then identify the users and groups allowed to use that particular db. You also need to set permissions. This is where you would define RO and RW abilities.
I don't have any books on MS Access Security to point you to, but you have to learn up on that in order to accomplish what you want to do.
I guess you code write VBA code also, but if you're not using the Security feature, Access defaults to all users accessing a db as Admin and without some foolproof way of doing it, writing code would have it's drawbacks.
Upvote
0
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
This is a method that I have used successfully. You will need:
1. A table called tblStaff, with at least the fields Login and Permissions. Both are Text fields.
(a) In Login, enter the Windows login name of each user who will have ability to add or change data. Those whose names do not appear here will have read-only access to the database.
(b) In Permissions, give them values Edit or Admin
2. A form that opens as your main menu. In this example the form is frmMenu. On this form you need 2 hidden textboxes called txtUser and txtLevel.
In the Form's Load event you will need this code:
If you don't know how to do this, here's how:
Go to the form's Properties.
Click the Events tab.
Double-click the blank Load row, so you see Event Procedure.
Click the Builder (...) button to go to the code window.
Paste this code in, replacing this stuff:
With the code you copied.
3. On each form that opens you need this in the Form_Load event:
Denis
1. A table called tblStaff, with at least the fields Login and Permissions. Both are Text fields.
(a) In Login, enter the Windows login name of each user who will have ability to add or change data. Those whose names do not appear here will have read-only access to the database.
(b) In Permissions, give them values Edit or Admin
2. A form that opens as your main menu. In this example the form is frmMenu. On this form you need 2 hidden textboxes called txtUser and txtLevel.
In the Form's Load event you will need this code:
Code:
Private Sub Form_Load()
Dim sPermit As String
Dim iAccess As Integer
Dim Ctl As Access.Control
Me.txtUser = Environ("username")
If IsNothing(Me.txtUser) Then
sPermit = "ReadOnly"
Else
sPermit = GetPermission(Me.txtUser)
End If
Select Case sPermit
Case "Edit"
iAccess = 2
Case "Admin"
iAccess = 3
Case Else
iAccess = 1
End Select
Me.txtLevel = iAccess
End Sub
Private Function GetPermission(sUser As String)
If (IsNothing(DLookup("Permissions", "tblStaff", "Login='" & Forms!frmmenu!txtUser & "'"))) Then
GetPermission = "ReadOnly"
Else
GetPermission = DLookup("Permissions", "tblStaff", "Login='" & Forms!frmmenu!txtUser & "'")
End If
End FunctionGo to the form's Properties.
Click the Events tab.
Double-click the blank Load row, so you see Event Procedure.
Click the Builder (...) button to go to the code window.
Paste this code in, replacing this stuff:
Code:
Private Sub Form_Load
End Sub3. On each form that opens you need this in the Form_Load event:
Code:
If forms!frmMenu!txtLevel = 1 Then
Me.AllowEdits = False
Else
Me.AllowEdits = True
End If
Upvote
0
btrede
Board Regular
- Joined
- Oct 4, 2005
- Messages
- 87
Could you modify the switchboard?
You know Denis, this is an awesome script! I would like to know if it would be possible to add this the an existing switchboard, so when the database opens, it runs this script when the switchboard is opened? What do you think? Any suggestions?
Regards,
~~Bradley
You know Denis, this is an awesome script! I would like to know if it would be possible to add this the an existing switchboard, so when the database opens, it runs this script when the switchboard is opened? What do you think? Any suggestions?
Regards,
~~Bradley
Upvote
0
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
Hi Bradley, glad you like it! 
I haven't found a way to do this with a Switchboard but here's the rest of my setup -- this uses a single textbox on the menu form, which I call lstLauncher.
You will need:
1. A table called MenuItems, with these fields --
2. The listbox called lstLauncher, with this RowSource --
3. In the Click event of the listbox, this code --
You control what users can go to by adding records to MenuItems.
Control the order of the list by edting the SortOrder field.
When users click, the code checks whether the object is a form or report, and runs the appropriate action.
If you also need to launch queries, add another Case statement to the OnClick event to deal with them.
Denis
I haven't found a way to do this with a Switchboard but here's the rest of my setup -- this uses a single textbox on the menu form, which I call lstLauncher.
You will need:
1. A table called MenuItems, with these fields --
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | MenuItemstable | |||||
| 2 | ||||||
| 3 | Field | DataType | Comment | |||
| 4 | Item | Text | Descriptivename | |||
| 5 | Form | Text | Objectname(realnameindb) | |||
| 6 | ObjectType | Text | FormorReport | |||
| 7 | SortOrder | Number | Usedtosetorderinlist | |||
Sheet1 | ||||||
2. The listbox called lstLauncher, with this RowSource --
Code:
RowSource for listbox (lstLaunch)
SELECT MenuItems.Item, MenuItems.Form, MenuItems.ObjectType FROM MenuItems ORDER BY MenuItems.SortOrder;3. In the Click event of the listbox, this code --
Code:
OnClick event for list box (lstLaunch)
Dim sForm As String, sType As String
sForm = Me.lstLaunch.Column(1)
sType = Me.lstLaunch.Column(2)
Select Case sType
Case "Form"
DoCmd.OpenForm sForm
Case "Report"
DoCmd.OpenReport sForm, acViewPreview
End SelectControl the order of the list by edting the SortOrder field.
When users click, the code checks whether the object is a form or report, and runs the appropriate action.
If you also need to launch queries, add another Case statement to the OnClick event to deal with them.
Denis
Upvote
0
Similar threads
- Question
