Lock one table?

[connie]

There is a main table that users are not supposed to go into at all - there are forms for that purpose - and yet someone keeps going in there. Obviously I am afraid of the worst. There is a huge amount of data that could be lost if this person hits a wrong key, somehow causes an irreperable crash, or edits any info at all (audit trail wouldn't be able to record it).

The database is in Access 2003 for these users. I know that I can hide the tables but unfortanately I think that would soon be worked around via the menu...some users know enough to do major damage and not enough not to, if you know what I mean.

Anyhow, this database goes back and forth between two totally different networks and so I probably cannot set up workgroup permissions in the way Microsoft suggests for "protecting a database." There must be a way to lock &/or password-protect a single table, right?

 

[Mr. B]

First, I am assuming that you are useing a split database because you have multiple users. If you are not, stop what you are doing right now and split the database. I would add functionality to my front-end that would not allow my users to even get to the tables at all, period.

First, use code to disable the use of the "Shift" key when opening the front-end. Next, make each form so that when it closes, it opens the previous or calling form. Provide all necessary navigation buttons to get users through the entire processes and functionality of your database. Remove all options for closing forms and/or opening them in design view. Remove the option to close the database with the "X" in the upper right corner. Auto load your first form and hide the Database window to users. Distribute a mde type file which will not allow users to get to the code behind the database.

It takes some work, but you can keep users out of the tables and you need to do this.

 

[connie]

First, I am assuming that you are useing a split database because you have multiple users. If you are not, stop what you are doing right now and split the database. I would add functionality to my front-end that would not allow my users to even get to the tables at all, period.

I followed the instructions here and split the database per your suggestion. I also went in and made the tables hidden and the database window not appear on Startup. However, if the user goes and figures out how to redisplay the hidden parts, tables that are now supposed to be "back-end" still show up in the database window (except with a different icon), and they still open. Why is that?

First, use code to disable the use of the "Shift" key when opening the front-end.

How might I do this? In VBA? And what does this prevent?

Next, make each form so that when it closes, it opens the previous or calling form. Provide all necessary navigation buttons to get users through the entire processes and functionality of your database. Remove all options for closing forms and/or opening them in design view. Remove the option to close the database with the "X" in the upper right corner. Auto load your first form and hide the Database window to users. Distribute a mde type file which will not allow users to get to the code behind the database.

Ok, got all of this except the mde type file. Could you elaborate on this one pls?

Thank you soooo much!!

 

[Galaxiom]

An mde is an Access Executable. It compiles the whole thing, making the design entirely inaccessible. The Access 2007 version is accde.

Be sure to keep a copy of the mdb file in a safe place or you will never be able to modify the design again.

 

[Mr. B]

First, an "mde" file is one where the VBA code is "compiled" and there form is not accessable to anyone, including the developer. This simply provides a layer of security for any code that you have in your applicaiton. Any mdb file can be converted to an mde file by simply using the option from the "Make MDE file ..." option from the Tools/Database Utilities menu.

As is true with may things that you do in Access, there can be several steps to actually accomplishing and entire goal. For example, setting up a combo box to display the desired data correctly can take several steps. It is not just as simple as creating the combo box. That is only the first step.

Just creating and distributing an mde type file does not prevent users from being able to get to tables and other objects. If you create your application and select a form as the "startup" form for you application or even if you create an "AutoExec" macro to run some code and open a specific form at startup, if a user holds down the "Shift" key and continues to hold it down until the file is opened, all of your startup options will be bypassed and the user will be looking at the database window just as if you had done nothing to control how your application starts. This is true even if you have created an mde type file and distribute it to users.

If you want to keep users out of of the tables, queries and other objects completely, you must take the time to implement most of the things that I listed in my first response.

As for how to do this, here is a couple of links that should help:
http://www.mvps.org/access/modules/mdl0011.htm
http://www.mvps.org/access/general/gen0040.htm

As I stated before, it takes some extra effort to keep, otherwise well intentioned hands out of any applicaiton where they can really mess things up.

As for the table names still showing; Yes you are seeing icons that indicate "linked" tables. These tables are seen by Access just as they would be seen if they were a native table except that they actually exist in another file. The reason for "splitting" and database is "NOT" to limit access to tables, in fact it it right the opposite. The reason for "splitting" a database file is to that each user can have their own copy of the "application" and only link to the actual storage part of the applicaiton. Having and trying to allow all users to "muddle" around in one file is like haveing a single Excel file and trying to have multiple users open and work in it. You can imagine the problems that that would cause. Well, it is the same thing when you try to use a single Access file and have multple users use it. I know that there have been and continue to be situations where the "single" file type method has been and is being used, but believe me when I tell you that it is only a matter of time until there will be problems.

Please feel free to post back with any and all questions as you move forward with your project.

 

[connie]

First, an "mde" file is one where the VBA code is "compiled" and there form is not accessable to anyone, including the developer. This simply provides a layer of security for any code that you have in your applicaiton. Any mdb file can be converted to an mde file by simply using the option from the "Make MDE file ..." option from the Tools/Database Utilities menu.

Hmm. So I won't be able to get into my own code? I don't like the sound of that! Well, I do keep a constant backup of the database for just in case situations...also there are a variety of people that may access the database from time to time but only in sense of viewing reports. As far as entering/editing data, there are only 2 of us who do so, myself the Admin and 1 user, who wouldn't know the first thing about viewing the VBA, so I think I can skip this step.

If you create your application and select a form as the "startup" form for you application or even if you create an "AutoExec" macro to run some code and open a specific form at startup, if a user holds down the "Shift" key and continues to hold it down until the file is opened, all of your startup options will be bypassed and the user will be looking at the database window just as if you had done nothing to control how your application starts. This is true even if you have created an mde type file and distribute it to users.

I have a startup switchboard that auto launches and have the database window and tables hidden. Re: disabling the shift key: although it's probably not necessary, I'd like to do it. Is this link you sent the same thing?

The reason for "splitting" and database is "NOT" to limit access to tables, in fact it it right the opposite. The reason for "splitting" a database file is to that each user can have their own copy of the "application" and only link to the actual storage part of the applicaiton.

Well, I undid the splitting of the database for now (overwrote it with the unsplit backup) so that I can learn a little bit more about it. As mentioned above, there are only 2 of us editing - one of whom is under strict instruction as to how to do so - so I'm hoping this won't be an issue. However I would like to split the database if in fact this makes both of us able to perform updates at the same time. I read that that is one of the benefits of splitting the database. How is that possible though? Also, I take the database off of that computer and network and make edits on my local computer, where I use Access 2007. Its home, though, is on a totally different network and is in Access 2003. Will the different editions and/or different networks be a problem if I split the database?

I would add functionality to my front-end that would not allow my users to even get to the tables at all, period.

Going back to one of your original comments: I guess I'm still not clear on how it is that I can prevent the user from being able to access the table. I've hidden it, and the database window, and made the navigation seamless (switchboard, menus, forms, reports, etc.). But if the user opens Tools -> Startup that can be temporarily changed if user really tries to get into those tables.

Oh, and can I remove the top toolbar as well?

Thanks!!

 

[Mr. B]

connie,

So I won't be able to get into my own code?

Only in that specific mde file. You create a new mde file each time you make changes to your application. You always keep multiple backup copies of your mdb file. The mde is only for distribution to other users. I have had databases that worked for years when distributing the mdb file. So if you want to skip this step, then so be it.

I have a startup switchboard that auto launches and have the database window and tables hidden. Re: disabling the shift key: although it's probably not necessary, I'd like to do it. Is this link you sent the same thing?

Yes the link is the same. Just be sure to keep a backup copy of your database file and be sure that you understand make provisons for how to reset the "allowShiftKey" so you can get into the file. When you implement restricting the Shift key, no one, including you will be able to hold the Shift key down and get past your form that loads at startup. Hide the database window in the startup options.

Well, I undid the splitting of the database for now (overwrote it with the unsplit backup) so that I can learn a little bit more about it. As mentioned above, there are only 2 of us editing - one of whom is under strict instruction as to how to do so - so I'm hoping this won't be an issue. However I would like to split the database if in fact this makes both of us able to perform updates at the same time. I read that that is one of the benefits of splitting the database. How is that possible though?/QUOTE]
I would highly recommend that you return to your split method, even if ther are only two of you using the database. It is not a matter of how many use the file, it is a matter of record locking, etc. As for it being a issue, as I have said before, it will be an issues sooner or later. I susposed that having the tables in another file could be considered as a way of keeping others away from the tables, seeing that they cannot make design changes to and linked table. The design changes must be made by opening the backend directly and making changes.

Will the different editions and/or different networks be a problem if I split the database?

If you do not convert the older version to the 2007 format then there should not be a problem.

Going back to one of your original comments: I guess I'm still not clear on how it is that I can prevent the user from being able to access the table. I've hidden it, and the database window, and made the navigation seamless (switchboard, menus, forms, reports, etc.). But if the user opens Tools -> Startup that can be temporarily changed if user really tries to get into those tables.

Oh, and can I remove the top toolbar as well?

If you will look in the Startup options, you can remove most of the functionality that is normally present when Access opens by unchecking several of the options. And, yes, if you need to, you can create your own menu bar and replace the standard bar with yours. At any rate, to start with, just make changes in the startup options and then try you application to see how it behaves. Then make other changes and test until you have the look and feel that you need. But be sure that you implement "disabling the Shift" key along with the options from the startup. This will solve the problem of any one getting to the tables directly.

 

[gemma-the-husky]

this sort of stuff is not straightforward to do in access by any means

access was designed as a database to be used in a similar way to excel, say - where users understand and interact with data directly.

what we often try to do however, is restrict what users can do and cant do - which is quite difficult to do, and needs both programming skills and an understanding of what is going on in Access. Imagine trying to do this in excel - so that users can only use certain cells, and where you control exactly what they can enter into those cells - not easy at all. Access is intrinsically more complex still as it is always a multi-user application.

This means however, that when users want additional functionality, developers are providing the facilities for them, to do what they could do themselves, if they had the knowledge. The problem is that because they dont have the knowledge, they could damage actually easily damage shared company data, and therefore have to be insulated from the full power of a dbs.

so, one way to do this is to have some sort of login functionality (either using access facilities, or writing your own) and then programmatically locking down certain features depending on who has logged in. - and a variety of things that can help with this are being discussed above.

 

[boblarson]

Imagine trying to do this in excel - so that users can only use certain cells, and where you control exactly what they can enter into those cells - not easy at all.

Actually, it is rather easy to do that in Excel, by the way. You can lock down everything but the certain cells you want and then you do have the ability to do some coding, or you can use a user input form to validate just like an Access form using the Before Update event, etc.