Hi Karl,
First things first - when you have a split database, every user must have
their own copy of the front-end. You statement that "... there is only one
shortcut to the new secure file." implies, to me, that you are attempting to
share the front-end between users. This will not work - it gives the worst
of both worlds. Each user needs their own copy of the (secure) front-end
file, and their own shortcut pointing to it and the workgroup file (the same
workgroup file must be used by all users).
Next, you must secure both the front-end and back-end files using the same
workgroup file. Join your secure workgroup file (as a member of the Admins
group) and set the permissions for all objects in the back-end. For other
than Admins group members, you can set the permissions of all tables to
none; the only thing you need to set is permission for the Current Database
to Open/Run to yes for all groups (except Users - you of course should have
no permissions). This allows the front-ends to link to the tables in the
back-end. You set the permissions you require for the tables on the linked
table objects in the front-end file.
The Workgroup Admin dialog is the easiest way to set up or delete user
accounts. You cannot edit an existing account by changing a username - all
you can do is add or delete users. And you cannot change the password for
any user account other than your own - all you can do for other users is to
clear the current password (and tell the user to set a new one!!!). You can
(and should) set a default password when you set up a new user account, but
unless you subsequently log-in as that user with the initial password (which
you can only do if the user hasn't changed it - and you should have educated
your users to change it immediately after you tell them what it is), you (as
an administrator) cannot change any user password other than your own. I
have one large application which uses User Level Security, and in that I
have a form which allows users to change their password. It's a simple form
with three textbox controls (for current password, new password and new
password confirm) and a button, with the following code:
Private Sub btnNewPW_Click()
'Last edited: Rob Parker, 28-01-2006
Dim ws As DAO.Workspace
Dim usr As User
On Error GoTo btnNewPW_Click_Error
Set ws = DBEngine(0)
Set usr = ws.Users(CurrentUser())
If txtNewPW = txtConfirmPW Then
usr.NewPassword Nz(txtOldPW, ""), txtNewPW
Forms("frmMenu").Visible = True
DoCmd.Close acForm, "frmChangePassword"
Else
MsgBox "Entries do not match. Try again ...", vbExclamation, ""
txtNewPW.SetFocus
End If
ExitPoint:
Exit Sub
btnNewPW_Click_Error:
If Err.Number = 3033 Then 'incorrect password
MsgBox "Incorrect current password. Cannot set new password.",
vbExclamation, ""
txtOldPW.SetFocus
Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
btnNewPW_Click of VBA Document Form_frmChangePassword"
End If
Resume ExitPoint
End Sub
It's possible to add and delete users via VBA code, but I find that it's not
worth the bother - after all, it's something which happens rarely (at least
for my app - only when project staff change, and that's not often), and I
(as an admins group member) can get to the workgroup Admin dialog, while no
other user can.
For your last question, regarding the Workgroup Admin in A2007, I can't
offer a definitive answer, since I don't use A2007. However, A2007 does not
support ULS (user Level Security) unless you save you database in an earlier
file format (ie, as an .mdb file). If your database is an .accdb file, I
suspect that the Workgroup Admin will not be accessible, since it's not
required.
And a final point: if you have code to re-link back-end tables to the
front-end, make sure that it works when the back-end is secured, and the
code (ie. the fornt-end) is being run by a user with no permissions on
back-end tables. There is sample code in the Microsoft Access Security FAQ
which will do this.
HTH,
Rob
BTW. If you have further questions regarding Access ULS, you're likely to
get a quicker answer if you post to the microsoft.public.access.security
group, rather than this general group. And you're much more likely to get a
response from an MVP who specialises in the topic.
