A2003: disallow access to Tables, Queries, etc.

[bulrush]

A2003 on Winxp

In my application I want to check the username. If the username is not mine I want to disallow access to Tables, Queries, Forms, Modules, Macros.

How do I do that in VBA?

Is this called the "Database Window" under Tools, Startup options?

 

[Mr. B]

bulrush,

From your post I have to assume that you have multiple users using your database.

The answer to your question is not a simple as it might sound. Although you can disable the various options in the Startup Dialog box, this will not prevent the knowledgable user form accessing the objects in Access.

First, are you using a "Split" applicaiton approach to deploying your database to multiple users? If not, please consider doing so.

Next, I would suggest that you have your development version and a deployment version that other users use. That version would have the options disabled from the startup options dialog box and would also have the use of the Shift key when opening the database disabled. I would have the database designed in a way that the use can only move through it in the paths through which I provide them opportunities by designing a menu system or command buttons that they are required to use. I would remove any opportunity for the user to close any forms by any other method other than the command buttons that I provide.

I would also create an mde type file and distribute that file to users while having the data storage file in a folder on a shared drive.

I could go on but as you can see, there is a little more to what you are wanting to do than just a few menu choices or check box options.

Others here may have more to share on this subject.

If you need more info, please just post back.

 

[bulrush]

Mr. B,
No it is not a split application. I am storing the actual MDB file on a network drive. Your advice is sound for a large user base, but not for this particular application. We will only have 2 users for this database and it is not worth the extra time to do what you mentioned.

I am more concerned about accidental deletion or renaming of objects (tables, queries, forms, etc) than malicious intent.

That said, years ago I thought there was a way, via VBA, to change startup options, and this simplified approach to security is what I was asking about.

Only about 10% of my job is Access development, thus it is almost always more prudent, from a time standpoint, to stick with what I know instead of involving a long learning curve for something I may never use again. Does that explain better the reason for my simplified approach?

 

[Mr. B]

IMO, it does not make any difference if you have 2 or 20 users using an Access database at the same time, it still is prudent to split the applicaiton. It is not difficult and will certainly help with certain issues. Besides, this is the method as designed by Microsoft for multiple users using a database file. Even if you do not create the mde file, at least consider splitting the database with two mdb files. I really don't think you would ever consider having one Excel file and have muptiple (even if it was only 2) users try to open and user the same file.

I do realize that you feel that it is Ok for your situation and that is just fine, as long as you understand the potential for problems and you have made an informed decision.

I have not actuall tried setting or resetting the startup options using VBA, simpley because I have not needed to, but I would assume that everyone of the options can be reset. However, to my knowledge there is not a "one statement" that will do what you want.

If you did use the split method, you would not need to use any VBA code to set the options as you can just set them for the file you send to the other user. Then you use the one that has no restrictions. Both are simply linked to the backend database file on the network.

I know I am really harping on the split thing, but believe me, it is a really critical issue.

Good luck with your project.