Securing Access database

Question

I am trying to secure the database with methods more reliable than hiding the navigation panel and hiding the back-end file. Is there a way to completely password-protect all objects from being modified or accessed? Would this cause trouble when they attempt to modify table data through a form? If so, I'm guessing it would be possible to temporarily unlock the tables using VBA.

Answer 1

Ok so I had to do 4 things:

1. Encrypt the back-end. This makes it so they need a password to open the back-end file. But they can still access the tables and stuff from the front-end! The next steps are to prevent that.
2. In the front-end, uncheck: Display Navigation Pane, Allow Full Menus, and Allow Default Shortcut Menus from the options. This makes it so none of the development stuff shows when initially opened. But they can re-check those options themselves if they go to the place where you uncheck them. To remove their access to the options in the File menu...

3. Create a table called USysRibbons. It should have two properties: RibbonName (key) and RibbonXML. Put whatever name you want in RibbonName, but in the RibbonXML, put:

   <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <ribbon startFromScratch="true"> <qat> <sharedControls> <control idMso="ImportExcel" label="Import from Excel" enabled="true"/> </sharedControls> </qat> </ribbon> <backstage> <button idMso="ApplicationOptionsDialog" visible="false"/> </backstage> </customUI>

It's important to set <ribbon startFromScratch="true"> because it prevents users from right-clicking the ribbon or quick access toolbar, selection customise ribbon, and accessing the menu from there. The stuff in the <qat> tags is just adding an Import from Excel button in the quick access toolbar.

Then go to Options > Current Database > Ribbon Name and select the ribbon record you just made. But if they hold the SHIFT key while opening the file, they can see all the objects, options, etc.! To prevent this, we need some VBA to disable and enable that option.

4. In the VBA editor, put something that accomplishes something similar to this:

   If InStr(globalstrPermission, "admin") <> 0 Then 'If logging in as the admin
     Dim prop As Property
     On Error GoTo SetProperty 'if property is already set and appended, skip down to the popup
     Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False) 'create and set the property 
   
     CurrentDb.Properties.Append prop
   
   SetProperty:
     If MsgBox("Do you wanna turn on bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
       CurrentDb.Properties("AllowBypassKey") = True 'MAKE SURE THIS SAYS TRUE OR YOU WILL LOCK YOURSELF OUT OF DEVELOPER TOOLS FOREVER
     Else
       CurrentDb.Properties("AllowBypassKey") = False
     End If
   End If
   

Now when that function is run and you select "no", it prevents the SHIFT trick from working. But if you want it to work, log in and click "yes" and close the database and open it with SHIFT.

My wording gets much more elementary as the post dragged on, but hopefully it's still easy to understand.