• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

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

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
1 Solution
Patrick MatthewsCommented:
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.


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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
jazjefAuthor Commented:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
jazjefAuthor Commented:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now