Solved

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
7
539 Views
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
0
Comment
Question by:jazjef
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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.

Regards,

Patrick
0
 
LVL 11

Expert Comment

by:BillPowell
Comment Utility
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.
0
 
LVL 84
Comment Utility
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.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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.
0
 
LVL 4

Author Comment

by:jazjef
Comment Utility
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.



0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
You can disable the Shift key:

http://www.mvps.org/access/general/gen0040.htm

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 ...
0
 
LVL 4

Author Comment

by:jazjef
Comment Utility
LSM:

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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now