Disable Design View on a Database

Note: I wrote this FAQ specifically for Access 97. I believe the techniques here will also apply to Access 2000, but I offer no guarantees since I've never designed an Access 2000 database.

Have you slaved for weeks designing the perfect database only to have your users delete or modify your forms and queries the first day they use it?

Do you have a user in your company who just finished attending a beginning Access class and wishes to "improve" your database design? (5 minutes later it no longer works)

If you answered yes to either of the above questions then this FAQ is for you! I have struggled in the past with users constantly poking around in my VBA code, so as a result I have managed to find and lock all possible ways a person can get to the design view of a database. Now, if your users are familar with database properties and also know how to write VB scripts to manipulate those properties, then they will be able to hack into your database, but if your users know about and can use these tools they probably know more about database design than you do. Let them in, they may improve the database!

Another note, these tips will not protect the data in your tables from tampering. They will only protect your design structure and VBA code.

Step 1: If you haven't already, you need to design a splash screen or title form which the users will see when they open the database. This form should contain all the necessary buttons and gizmos needed to get to other parts of database that the users will use. Remember, they will no longer be able to go to the database window and just click the form/report/query they want to open, so you will have to provide an alternative for them.

Step 2: Make a backup or 2 or 3 of your database someplace on your hard drive or personal network drive. After finishing the steps below, the only way back into your database will be to write a VB script which can manipulate the database properties to let you back in. (Shhh, don't tell your users!)

Step 3: If you are comfortable with VB coding, I have written a few routines which will automate these startup routines for you, as well as let you as the designer into the database while keeping others out. If VB coding appeals to you more than doing this manually check out faq181-1172 otherwise keep reading.
Goto Tools ---> Startup menu. In the Display Form box select your wonderful, snazzy start up form that you want the users to see when they enter.
Uncheck "Display Database Window"
Uncheck "Allow Full Menus"
Uncheck "Allow Built In Toolbars"
Uncheck "Allow Toolbar/Menu Changes"
Click The Advanced Button and Uncheck "Use Special Access Keys" (This disables the F11 shortcut)

Step 4: Ok, you have a choice here. You can:
A) Uncheck "Allow Default Shortcut menus" on the startup menu.
B) Define a custom shortcut menu with no "design view" options on it and reference it on the startup menu. (Don't ask me how, I've never done it.)
C) Go through all of your forms and set the "Shortcut Menu" property to no.

I recommend option C because it's quicker than option B and your users may need shortcut menus on some reports.
I gave a locked down database to my users once and forgot to disable the shortcut menus. They used them to go directly into the design view of the form and changed the record source. Did I mention the importance of having backups?

Step 5: At this point, your database should be sealed pretty tight. There is only one more way to break into the database design. You have to shift into it. This means to open the database while holding down the shift key. It's a back door trick which not too many run-of-the-mill users know about. (Unfortunately, my users know it.) Opening a database with the shift key will bypass all of your startup routines and open directly to the database window. Right now, this is the only way you can get into the database yourself, but you're about to disable it. You have made a backup, right??

Rather than typing out how to do this, let me just point you over to Rhicks' incredibly well written FAQ181-143. That's where I learned this piece of code. He explains how to disable this loophole and offers an idea for a secret way to re-enable it. Here are a few more ideas:
A) Transparent buttons. This is Rhicks suggestion, but what if your user accidentally finds it. (Ok, I admit it, I'm Paranoid!)
B) Secret password typed in a text box somewhere.
C) Code on startup which recognizes the user. If it's you, unlock the database otherwise keep it sealed.

Step 6: Whew! You're almost done. To my knowledge, there is now no way into your database except for your ultra-secret backdoor or writing a VB script to re-enable the shift trick. I don't care if you have designed the best database in the world, business rules change and sometime in the future your database will have to be modified. Right now you are the only person, (besides your resident VB guru), in the company who knows how to modify it. Cool! Time to ask for a big raise! :-D

No, seriously, what happens if you win the $150 million Powerball lottery tomorrow and decide to quit and move to Fiji, hmmm?? If your company is dependant on this database, there had better be someone left who can still modify it. I urge you to thoroughly document the "hidden features" which only you know about and leave this documentation somewhere where your boss or co-workers can easily find it if you are gone.

Now, I know this FAQ is getting kinda long, but I probably should include a few FAQ that I've heard from users who have been locked out. Hmm, a FAQ inside a FAQ.

Q: I can't see the database window any more! (Wahhhh!)

A: Good! <insert evil laugh here> LOL
Seriously though, find out why they want to see the database window. Do they want to run a report or query manually? Do they want to add data to a table. Set up a way for them to do this through your forms.


Q: I want to design my own queries and reports without bothering you. Your turn around time is several weeks and I need this data tomorrow!

A: If your users are capable of creating their own queries and reports show them how to link the tables into their own database. This way they can get to the data in the tables without touching your database design.


Q: I can't print my reports. The menus are gone!

A: If you open reports for users in print preview mode rather than sending them directly to the printer, you may hear this from the users. Just right click anywhere on the report. They can print the reports from the short cut menu. This is why you may not want to disable all short cut menus


That's all the advice I have for now. I hope it will be helpful to someone out there.