User Level Security Wizard (1 Viewer)

I am about to roll out my database to other users in the office.
I have split it into front and back end.
Now I would like to set some users as read only and admin level permissions.
The user level security wizard is greyed out and I have discovered that this is because my database is saved as a .accdb file.

What I would like to know is if I change it to a .mdb file what impact will that have on my database now that it is finished. Am I going to loose data or the format of data?

If it is going to cause problems, is there another way of creating user level security and leave it as a .accdb file?

Well, I would not do that. Aside from the code that might not be backwards compatible, going forward will create another issue. You need to roll out your own. Here's one suggestion (with additional links at the bottom of the page)...
https://www.access-diva.com/f7.html

Thanks for the info GinaWhipp there is some interesting information there.

All the links seem to relate to not being able to modify the layout or restrict access to areas etc. With the database I have, users are able to access all areas, but I don't want them to change any data or add data.

Is the only way to achieve this by changing it at form level? I'm just worried if I do it that way, if I make any changes to the .accdb file and then save the changes over as an .ACCDE file, that I will forget to change all the forms to read only.

I tried to password protect the back end, but that then causes errors when opening the front end.

Does that make sense?

In addition to Gina's suggestion see this video by Steve Bishop for ideas on User Access/Privileges. Steve has several videos on various Access features/usages.
Good luck.

You can use something like...
https://www.access-diva.com/vba6.html

To prevent certain people from editing. And since it's based on log-on (that you create) you don't have to remember to lock a form.

I'm entering this late, but the ONLY reasonable way to do this so that you don't forget is to make the forms smart enough to set their own form properties to prevent or allow certain types of modification. Which you can do at the form level via the form's .Allowxxxx properties, or at the individual control's .Enabled, .Locked, and .Visible properties.

You would do this in the Form_Load routine if you wanted all forms to be visible but some would not be usable for updates. Or at the Form_Open routine if you wanted some forms to just flat-out refuse to run.

GinaWhipp said:

Well, I would not do that. Aside from the code that might not be backwards compatible, going forward will create another issue. You need to roll out your own. Here's one suggestion (with additional links at the bottom of the page)...

Click to expand...

I liked the look of this link and have started to set up the Assign *Permissions* based on Users Level...
When I have got to step 4 in the New User Form procedure it won't allow me to open the Switchboard in Design View to do step 4.

I get an error message saying:
You can't switch to a different view at this time. Code was executing when you tried to switch views. If you are debugging code, you must end the debugging operation before switching views.

I can't even find the debugger to turn it off. I assume it is trying to debug the New User Form. Can you point me in the right direction?

When you get this message, a form is open somewhere and is running code. Which probably means that some form is opening at DB launch, after which that form hides itself and lurks in the background. So the question is, what forms are open? (And the answer is that the SWITCHBOARD form is probably the culprit.)

When you are going to do development with this kind of form, you need a way to shut it down because humans and Access forms both dislike the idea of you digging around in them while they are still conscious.

The most common way of doing this is that you DON'T auto-start the switchboard form in the copy that you are going to use for development. Instead, you keep a non-automated version (i.e. NOTHING launches automatically) as your Design Master version. Then, when you are ready to deploy, you have a checklist of things you need to do to set up launch-time automation. You do that to a COPY of the master. (You never EVER release your master copy.) This lets you get in and do the brain-salad surgery on your form's code or displayed content without running into that "hidden form running in the background" issue.

While you are at it, you might even consider keeping a test copy - i.e. you now are talking three copies: design, test, and production. If you have a split DB, then yes, you need three copies of both the FE and the BE. You need a checklist of steps to switch things from development to test to production. But the steps will always be the same - like relinking the FE to the corresponding BE; turning off the startup bypass and other special keys; and using the File >> Options part of the Access I/F to define the startup form and the editing, tab behavior, and allowed shortcut keys.

Then, before you go live, make one more copy: A backup of the production copy that is being promoted from a successful test. This has multiple advantages including the most important one - that you can easily recover from disasters and hacking in the production copy by just making another copy from the most recently promoted test copy.

Doc Man hit it on the head. You are trying to run the code which opens the same Form you are on. If that is not the case, need more details.

Okay thanks for that. I took the switchboard out of the start up and that fixed it. Now it is allowing me to view the switchboard in design view.

Thanks The_Doc_Man for the ideas about making extra copies for different purposes. I will do that. I had only been doing progressive backups as I change the database.

Now that I have got past that problem I am stuck on an error with the code for the User form that I am using from the access diva's idea. When I use the form the I get an error saying: Compile Error: Syntax error. As far as I can see everything is spelt correctly.

It is highlighting this string:
strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _" Values ('" & Environ("UserName") & "' , '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, 9, True"

I'm using Access 2016 not sure if that is the issue. Also when I get this form working will it call up the switchboard or will I need to add it back into the startup?

Hmm, try this line...


You tell it which Form to open, so whichever you want to open is the one that will open when this closes.

Thanks Gina Whipp. That worked!
Now I put my logon details into the form and they are recorded in the tblUsers as a 9(read only). When I access the data via the forms it allows me to change the data though which it shouldn't be being read only.

Well, looks like you forgot a part, go to the bottom of this page...
https://www.access-diva.com/f10.html

Under Examples you will see what to do in the On_Current of your Form.

Thank you Gina Whipp. I had left that out.
So does that code go on the switchboard? When I go to the On Current for the switchboard, there is an Embedded Macro already in there.

It says:
SetTempVar
Name CurrentItemNumber
Expression = [ItemNumber]

It goes in the On_Current event of the Form(s) you want to Lock\Unlock. The Switchboard should not be locked, at least, mine never are.

Thank so much for all your help I really do appreciate it!!

I have put in the code I was missing. Now it errors and highlights .txtSecurityID In the string that says
Me.txtSecurityID = rst.Fields("uSecurityID")

I'm certain that the unbound txtSecurityID field is spelt correctly on the switchboard. What else would cause this?

Is that field in the Table?

Yes, USecurityID is in tblUsers. txtSecurityID is an unbound field on the Switchboard. I have also dropped USecurityID on the frmNewUser which I selected through Add Existing Fields.

Is the one in the table numeric?

Yes, it's data type is set to Number.