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.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

680 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