Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How can I prevent users from accessing the 'design view' and making changes to my custom VBA form in MS Access?

Posted on 2006-07-08
Medium Priority
Last Modified: 2008-01-09
I have designed a form that uses a MS Access dbase. It default opens to the form mode...which is just fine. However, by going to the menu and choosing 'design view' I can simply delete controls and etc from this form and then choose 'form view' again, and  make the program crash because there are  controls missing etc.

How can I prevent users from accessing this 'design' view and making changes to the form? I know how to lock my VB code but I am unable to lockout user access to the 'design mode'.... can this be done? I can't create an MDE because it tells me I have too many tables etc in my database... Ideally, I would just like to lockout the design view access in some way...

thanks for any help
Question by:jazjef
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 93

Expert Comment

by:Patrick Matthews
ID: 17065449
Hi jazjef,

Normally I would recommend an MDE, but apparently you already tried that.  How many tables do you have?
Also, have you split the db into a front-end (which should have only Queries, Forms, Reports, and code) and
a back-end (which has the actual data, and thus the tables)?  Splitting the db that way should allow you to
convert the front-end to an MDE.

Another option is to implement Access's security provisions, which allow you to selectively grant or deny users
and/or groups the ability to enter the design mode of a Table, Query, Form, or Report.  I must admit, though,
that I am *not* a fan of Access's workgroup security, and I usually discourage people from using it.


LVL 11

Expert Comment

ID: 17065496
I suspect that you might be able to create an MDE if you Compact and Repair and Compile all your code modules.  There is a limit to creating an MDE (not sure the exact number), but its pretty high.  That message you mentioned often happens not because you have too many tables, but because the code is not compiled.
LVL 85
ID: 17066177
What version of Access are you using? When you say "I know how to lock my VB code", what do you mean? Are you using the VBA password? if so, I'd urge you to stop doing so - it can cause some pretty serious troubles, and has led to developers (who forget the password) being permenantly locked out of their codebase.

I personally have never run into a limit when creating an .mde file, but I suppose it's possible. As Bill said, you should be able to create an .mde file from your .mdb file. Often it's a good idea to import all objects to a new, blank db before doing so (since this strips out all superfluous stuff and provides a nice, clean container to build). If you do this, make sure to check your references to ensure that you've set all to match the old db. If you can create your .mde format, users will not be able to open your forms, reports, or modules in Design view.

From my perspective, there's nothing wrong with Access ULS IF it's properly applied and if you thoroughly understand how it's applied and what it can and cannot do. Most novice developers do have a rough time with it, since it is NOT as simple to understand as Access. This is the only way to truly lock people out of design view of your tables and queries, however.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 34

Expert Comment

ID: 17066262
Another option would be to create a custom Menu to display only items that you want your user to see...effectively removing the design options from the menu.

Author Comment

ID: 17066561
I may have solved my own problem/answered my own question to a certain degree. However, each respondent thus far has in fact given me information that is new to me and has taught me something about the issue. Being that this is a new issue for me, any and all of it is helpful. Please let me know if there is a better way.

Limit on 'Tables' appears to be 2048 for my version of Access 2003. This alone would prevent me from creating MDE (according to my research) due to the number of fields that one 'record' consists of in my database and the total number of records in my dbase.

I have found that UNchecking the following items under  'Tools' >> 'Startup' will cause the 'design view' option to be hidden whenever you open the VBA form with its dbase:

'Allow Full Menus'

'Allow Default Shortcut Menus'

However, by holding the SHIFT key when opening the dbase and subsequent form, one can allow these menu items to load again. The people I am writing this for are only concerned with my creating a method that will prevent them from accidentally changing anything ----they merely want me to protect them from themselves.  :)

Regarding "Lock my VB code":
from VB code window choose >> Tools >> 'YourDatabaseName' Properties >> Protection tab >> Checkbox: 'Lock project for viewing' then set password

Regarding forgetting passwords: yes, that could be a problem...  good point---I should take steps to address this issue immediately.... thanks for highlighting that LSMconsulting.

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 17067457
You can disable the Shift key:


You have anywhere close to 2000 tables in your database? I can't recall every seeing a db that large. What are you storing that requires that many tables? Just curious ...

Author Comment

ID: 17076324

I believe that I am wrong about having that many tables. I have a database where each record has 12 demographic fields and an additional 124 fields that reflect numeric ratings responses to 124 individual questions---there are 1,788 records in the dbase. So, you still haven't encountered a db that large yet. I am able at the moment to satisfy the fiber of my question by my earlier posting that 'hides' the design view from users. I'm a newbie working with VBA as it interfaces with Access DB... I know VB but am learning the intricacies of Access and SQL as part of my 'self-improvement-as-a-programmer' plan for 2006......

Thanks greatly for the code link on disabling the Shift key, it's extremely helpful to know that this can be done in this way.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question