Access 97 Security Problems, need help

[GTLoco99]

Hello all!

I have a database I created and it currently has three users. My self (Admin) and two other users (UserA, UserB for example purposes). I have never like Access 97 security so I wrote my own security module. My problem is, now I need to restrict access to full menues and I can not do it with my current module. Here is the Code for my Security Login Form

Code
______________________________

Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As Database, prp As Property
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

Private Sub Exit_Click()

DoCmd.Quit acQuitSaveNone

End Sub

Private Sub Ok_Click()

Dim db As Database
Dim rs As RecordSet
Dim SQL As String

Dim User As TextBox
Dim Pass As TextBox


Set User = Me.User_ID
Set Pass = Me.User_Password

Set db = CurrentDb()

SQL = "SELECT User_Table.* FROM User_Table WHERE User_Table.User_ID Like '" & User.Value & "';"
'Checks user name and password against table
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

If rs.EOF Then
MsgBox "Username or Password is Incorrect! Try re-entering your Log In Information.", vbInformation, "Login Incorrect"
GoTo Exit_Sub
End If

If rs.Fields("User_Password&quot.Value = Pass.Value Then
GoTo Open_Item
Else
MsgBox "Username or Password is Incorrect! Try re-entering your Log In Information.", vbInformation, "Login Incorrect"
GoTo Exit_Sub
End If

Open_Item:

Select Case rs.Fields("User_Group&quot.Value

Case 0

DoCmd.OpenForm "Switchboard", acNormal, , "[ItemNumber] = 0 AND [SwitchboardID] = 1"
DoCmd.Close acForm, "Login_Frm", acSaveNo
Application.MenuBar = ""
ChangeProperty "AllowFullMenus", dbBoolean, True

Case 1
DoCmd.OpenForm "Switchboard", acNormal, , "[ItemNumber] = 0 AND [SwitchboardID] = 1"
DoCmd.Close acForm, "Login_Frm", acSaveNo
ChangeProperty "AllowFullMenus", dbBoolean, False

Case 2
DoCmd.OpenForm "Switchboard", acNormal, , "[ItemNumber] = 0 AND [SwitchboardID] = 6"
DoCmd.Close acForm, "Login_Frm", acSaveNo
ChangeProperty "AllowFullMenus", dbBoolean, False

End Select

Exit_Sub:

rs.Close
db.Close

End Sub
_____________________________________

As you can see, I am trying to Use the Function ChangeProperty to change access to full menues, but the problem is, that this change does not take effect, unless the database is reopened. I can make this change take effect if I run the function in the AutoExec macro, but then I am unable to open a log in form for the user to log in as. So I am forced to use Access Security so I can use CurrentUser() to find out who is logged into the database.

Now that you have an idea of the background, let me get to my problem. In access 97, when you join a User Workgroup File (system.mdw for example) it affects all the databases on your system that are launched. This is a real problem for me. I only want the workgroup to apply to the current database. I have tried the code bellow in the AutoExec to get around this problem.

Code:
___________________________

Private Sub SetSystemDB()

DBEngine.SystemDB = "F:\Edu\CME Database\System.mdw"

Dim User As String

User = CurrentUser()

If User = "Admin" Then
ChangeProperty "AllowFullMenus", dbBoolean, True
Else
ChangeProperty "AllowFullMenus", dbBoolean, False
End If


End Sub
__________________________

I use the DBEngine.SystemDB property to change the registry key for the workgroup file. This from what I understand, will make the change for one time use. The problem is, that even though the workgroup is supose to pop a log in up, it doesn't, because the users have alreay opened and logged into the database with their default MDW. How can I get this MDW to work only for my database, and not all others?

Thanks
Glen

 

[GTLoco99]

Got it to load the workgroup for just this database.

Thanks
glen