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

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
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
LVL 92

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 84
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.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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