1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How to get to Design mode

Discussion in 'Business Applications' started by DKTaber, Jan 23, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    I must have a mental block. Have an Access 2000 application that I wrote for a non-profit organization 2-3 years ago, and have been modifying it from time to time as they wanted changes. There was never a concern about anybody attempting to change the design of any objects, so I just left the system with the normal menu- and toolbars... which, of course, gives users who know Access the ability to change designs.

    There is now a desire to protect the design against inadvertent changes. The best way would be to somehow "lock" the designs with a password. I don't want to just password protect the database, because that doesn't protect the designs of the tables, forms, etc. once the user gets in. I can find nothing in Access Help about protecting individual objects within the database. Is there, and how would one do it?

    If that's not possible, I thought about installing custom tool- and menu bars wherein the Design icon and "View" menu choice are removed. I realize that a savvy user could always customize those and put them back, but the users are for the most part not very Access-savvy. I have two questions here: (1) Is there a way to password protect tool- and menu bars so they can't be modified without the password? And (2) if I remove the ability to get to Design mode from the tool- and menu bars, how am I supposed to get to it when I need to make changes? Do I have to include in my customized bars one that has the ability to get to Design mode, and install it every time I want to make changes?

    This Web site has the best Access gurus in cyberspace, and I look forward to any guidance you can provide. Thanks.
     
  2. kbstoikop

    kbstoikop

    Joined:
    Dec 13, 2002
    Messages:
    45
    you would make an mde file from your database which you would distribute to the orginization.
    It allows it only allows you to run the database but make no changes to the design of any of the objects (forms,tables queries, macros, etc)

    when changes are made to the original database you create a new mde and re distribute it

    If your database is networked you must use a generic connection that will work for each computer such as \\systems\datafolder not mapping the network drive to a particular letter.

    in access 97 you use the tools/database utillites/make mde file so I would assume it is similar in access 2000
     
  3. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    I'm aware of .mde files, and have done that with very simple databases that would never need user intervention. Can't do that in this case, because I want some people in the organization to have the ability to write queries, and make minor changes that I lead them through on the phone. I'm in the process of reading/studying how to apply user-level security to objects. It uses terminologies I'm not familiar with, and seems pretty complicated, but I think it's what I'm going to have to do.

    Thanks for the response.
     
  4. THoey

    THoey

    Joined:
    Feb 12, 2001
    Messages:
    3,420
  5. kbstoikop

    kbstoikop

    Joined:
    Dec 13, 2002
    Messages:
    45
    there is nothing to stop you from using .mde files for your application and then using another database to run queries etc..
    Keep your data in seperate database and link your mdes and mdbs to that data.


    Object security is a can of worms,
    heaven forbid if the computers they are using uses different flavours of Windows

    Good Luck
     
  6. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    Went to the site, joined, but search engine is not working (being updated; get a message to that effect and "...thank you for your patience"). Will try tomorrow.

    Thanks for the tip.
     
  7. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    Oooops! Yes, they have different "flavors" of computers... all PC's, but with different versions of Windows. This is a non-profit organization; always short of $; computers not up-to-date and often hand-me-downs. I think I may try to persuade them to let me limit "security" to special toolbars with things like Design mode removed. I'm afraid that if I try to impose real security, it would turn out to be just what you say... a can of worms.
     
  8. THoey

    THoey

    Joined:
    Feb 12, 2001
    Messages:
    3,420
    Yeah, they are having a bit of trouble with the search. Here is the FAQ I mentioned (Borrowed from Tek_Tips):

    Note: I wrote this FAQ specifically for Access 97. I believe the techniques here will also apply to Access 2000, but I offer no guarantees since I've never designed an Access 2000 database.

    Have you slaved for weeks designing the perfect database only to have your users delete or modify your forms and queries the first day they use it?

    Do you have a user in your company who just finished attending a beginning Access class and wishes to "improve" your database design? (5 minutes later it no longer works)

    If you answered yes to either of the above questions then this FAQ is for you! I have struggled in the past with users constantly poking around in my VBA code, so as a result I have managed to find and lock all possible ways a person can get to the design view of a database. Now, if your users are familar with database properties and also know how to write VB scripts to manipulate those properties, then they will be able to hack into your database, but if your users know about and can use these tools they probably know more about database design than you do. Let them in, they may improve the database!

    Another note, these tips will not protect the data in your tables from tampering. They will only protect your design structure and VBA code.

    Step 1: If you haven't already, you need to design a splash screen or title form which the users will see when they open the database. This form should contain all the necessary buttons and gizmos needed to get to other parts of database that the users will use. Remember, they will no longer be able to go to the database window and just click the form/report/query they want to open, so you will have to provide an alternative for them.

    Step 2: Make a backup or 2 or 3 of your database someplace on your hard drive or personal network drive. After finishing the steps below, the only way back into your database will be to write a VB script which can manipulate the database properties to let you back in. (Shhh, don't tell your users!)

    Step 3: If you are comfortable with VB coding, I have written a few routines which will automate these startup routines for you, as well as let you as the designer into the database while keeping others out. If VB coding appeals to you more than doing this manually check out FAQ181-1172 otherwise keep reading.
    Goto Tools ---> Startup menu. In the Display Form box select your wonderful, snazzy start up form that you want the users to see when they enter.
    Uncheck "Display Database Window"
    Uncheck "Allow Full Menus"
    Uncheck "Allow Built In Toolbars"
    Uncheck "Allow Toolbar/Menu Changes"
    Click The Advanced Button and Uncheck "Use Special Access Keys" (This disables the F11 shortcut)

    Step 4: Ok, you have a choice here. You can:
    A) Uncheck "Allow Default Shortcut menus" on the startup menu.
    B) Define a custom shortcut menu with no "design view" options on it and reference it on the startup menu. (Don't ask me how, I've never done it.)
    C) Go through all of your forms and set the "Shortcut Menu" property to no.

    I recommend option C because it's quicker than option B and your users may need shortcut menus on some reports.
    I gave a locked down database to my users once and forgot to disable the shortcut menus. They used them to go directly into the design view of the form and changed the record source. Did I mention the importance of having backups?

    Step 5: At this point, your database should be sealed pretty tight. There is only one more way to break into the database design. You have to shift into it. This means to open the database while holding down the shift key. It's a back door trick which not too many run-of-the-mill users know about. (Unfortunately, my users know it.) Opening a database with the shift key will bypass all of your startup routines and open directly to the database window. Right now, this is the only way you can get into the database yourself, but you're about to disable it. You have made a backup, right??

    Rather than typing out how to do this, let me just point you over to Rhicks' incredibly well written FAQ181-143. That's where I learned this piece of code. He explains how to disable this loophole and offers an idea for a secret way to re-enable it. Here are a few more ideas:
    A) Transparent buttons. This is Rhicks suggestion, but what if your user accidentally finds it. (Ok, I admit it, I'm Paranoid!)
    B) Secret password typed in a text box somewhere.
    C) Code on startup which recognizes the user. If it's you, unlock the database otherwise keep it sealed.

    Step 6: Whew! You're almost done. To my knowledge, there is now no way into your database except for your ultra-secret backdoor or writing a VB script to re-enable the shift trick. I don't care if you have designed the best database in the world, business rules change and sometime in the future your database will have to be modified. Right now you are the only person, (besides your resident VB guru), in the company who knows how to modify it. Cool! Time to ask for a big raise!

    No, seriously, what happens if you win the $150 million Powerball lottery tomorrow and decide to quit and move to Fiji, hmmm?? If your company is dependant on this database, there had better be someone left who can still modify it. I urge you to thoroughly document the "hidden features" which only you know about and leave this documentation somewhere where your boss or co-workers can easily find it if you are gone.

    Now, I know this FAQ is getting kinda long, but I probably should include a few FAQ that I've heard from users who have been locked out. Hmm, a FAQ inside a FAQ.

    Q: I can't see the database window any more! (Wahhhh!)

    A: Good! <insert evil laugh here>
    Seriously though, find out why they want to see the database window. Do they want to run a report or query manually? Do they want to add data to a table. Set up a way for them to do this through your forms.


    Q: I want to design my own queries and reports without bothering you. Your turn around time is several weeks and I need this data tomorrow!

    A: If your users are capable of creating their own queries and reports show them how to link the tables into their own database. This way they can get to the data in the tables without touching your database design.


    Q: I can't print my reports. The menus are gone!

    A: If you open reports for users in print preview mode rather than sending them directly to the printer, you may hear this from the users. Just right click anywhere on the report. They can print the reports from the short cut menu. This is why you may not want to disable all short cut menus


    That's all the advice I have for now. I hope it will be helpful to someone out there.
     
  9. THoey

    THoey

    Joined:
    Feb 12, 2001
    Messages:
    3,420
    And, mentioned in that FAQ is the following for disabling the SHIFT key:

    How Do I Disable the Shift Key During Startup?

    Read All Carefully Before Proceeding!!!!!!!!!

    Here is two sub routines. One is to disable the shift key and the other is to re-enable the shift key.
    You only need to call the disable code one time and the shift key will remain disabled until you run the Re-enable code.

    The main thing to remember is that you need a way to run the Re-enable code from your open app, but you don't want the user to know how to do it. If you don't have a way to re-enable the shift key will have locked yourself out as well. I usually place a small transparent button on a screen that is seldom used by the user. I usually place the button in a corner where I know where it's at. The Re-enable code is called by the On Click event of this transparent button.

    To run the disable the shift key code, you do something similar to the above approach or call the sub how ever you wish. The thing to remember is "Have a way to Re-enable the shift key".

    Special Note...............
    Make a backup of you db before you start.

    '********************************************************
    'This Code Disables the Shift Key
    '
    Public Sub DisableByPassKeyProperty()
    Dim db As Database
    Dim prp As Property
    Set db = CurrentDb
    Set prp = db.CreateProperty("AllowByPassKey", dbBoolean, False)
    db.Properties.Append prp
    End Sub
    '********************************************************


    '********************************************************
    'This Code Re-enables the Shift Key
    '
    Public Sub EnableByPassKeyProperty()
    Dim db As Database
    Set db = CurrentDb
    db.Properties.Delete "AllowByPassKey"
    db.Properties.Refresh
    End Sub
    '********************************************************
     
  10. kbstoikop

    kbstoikop

    Joined:
    Dec 13, 2002
    Messages:
    45
    I work in non profit . we have 20 computers with 3 versions of windows, linux, bsdi, a mac and an as/400. I have already opened the can.

    We have a facilities management application that was written in access and the way we limit people to certain areas is to do custom menus.

    (pretty simple once you figure it out)

    we only tell certain people how to bypass the menus.
    and start the application at a certain form that you must exit from.

    This could also work for you.
     
  11. THoey

    THoey

    Joined:
    Feb 12, 2001
    Messages:
    3,420
    BTW, Not trying to take any business away from TSG. That is just another excellent site like TSG. I found this site from people at that site (Dreamboat)...

    Who says you can't belong to both?
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/114878

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice