database startup

[cg084]

Hello,

I am trying to get a database to start up in a manner that all users of the database will have the locking set to the same settings. I would have done this with the security wizard but they do not want the database secured. The users have quite a large lack in confidence using the MS Access 2k package.

I need the database to set the acces level to shared with Edited record locking. I also require to set the Open database with Record Level Locking activated.

I am thinking that this will have to be done in VB since my experience with VB is very limited i hope someone can help.

Any assistance is greatly appreciated.

Cheers CG084

 

[smuckers]

Good Afternoon cg084!

Hmm... I have used various security schemas without the Security Wizard. The code snippet below will restrict access to forms, buttons, controls or other objects.

You'll need a couple of tables that will hold the following information:
1. Users Names
2. Passwords
3. Ability or Security levels

So, using a Log In form(modal) and setting the UserName to a global variable or Class property you will then need to perform the following functions.

1. Check the Password against the Username.
2. Check the UserName against the Security level.

Once that is done you can re-use the Username and it's associated security level for every form or action performed.

For instance, as you open a form you can restrict access to the buttons or disable controls. I first use this code to limit the switchboard options that users see if they are not at an "Admin" security level. I also re-dimension the switchboard to make it so the swithboard is sized proportionally. This "sizing", whether it's for 5 switchboard buttons or 8 will save a lot of questions asked by the users concerning missing buttons or empty spaces.
**************************************************
If GetAbility(UserName) = "Admin" Then
Me.txtBox1.Visible = True
Me.txtBox2.Visible = True
Me.txtBox3.Visible = False

ElseIf GetAbility(UserName) = "Manager" Then
Me.txtBox1.Visible = True
Me.txtBox2.Visible = True
Me.txtBox3.Visible = False

ElseIf GetAbility(UserName) = "Operator" Then
Me.txtBox1.Visible = True
Me.txtBox2.Visible = False
Me.txtBox3.Visible = False
End If
***********************************************

You can use the DLookUp command for the GetAbility function. Don't forget error checking and nulls.

Also, represent all tables with a query. So, Unless they have the right permission level then the query will not run or display the data.

Finally, there are the StartUp Options that come with MS Access. You can use those too.

Mike Groh, Technical Editor of Access-VB-SQL magazine has this suggestion in the September 2002 issue, Advisor Answers column.

"Add all your public code to modules in a separate .MDB file, then use Tools>>Database Utilities>> Make MDE to make the MDB into a code library. A MDE , of course, is a special read-only version of your databae with all VBA code stripped out."

"Then, in your production datase, use Tools>>References to add a reference to the MDE code library."

I hope some of these ideas help. It's worked for me.

Good luck,

Sincerely,

Smuckers