Access Password Protect Question????? 1

[shadyness]

I am a somewhat new to db administration and design, but I have been asked to create a new feature in a pre-existing Access 2000 db. The client wants a button command to have multiple password protects. Basically, several (5) consulting firms will be using a single dbase and the client only wants each user to have access to only their particular information (ie-frm screens, tables, etc.). We have talked about splitting the data base into several linked ones, each with their own separate password, and utilizing a switchboard to send the appropriate user to their respective access base, but their has to be a more effiecient way, right?

 

[JimHorton]

With user-level security, you can grant/restrict row-level data and even field level data.

In a broad sense (you've much detail to work out) the idea is the following. For each firm, give them a group or user name. No one gets read permission on the table's data. For each of the 5 firms write a separate query with the criteria being that firms id for the specified table/s used. That query is to be 'with owneraccess option', and only the appropriate firm will have open/run rights to that query.

Now, each firm runs the form, (could be one form shared for all firms, the preffered way) or a separate one for each firm. When you load the form, based on the currentuser, you set the recordsource to be the proper query--an efficient way to do this is to suffix the query's name with the username, then the statment can upon loading the form is:
Myform.recordsource = "qryFormWhatever_" & currentuser

Now, even if another firm were to 'see' the other query, he couldn't open it, nor could he open the raw table, but he can open his own query/form, and see his own rows/fields.

Much more to it, but you get the picture,
--Jim

 

[JimHorton]

shady,
I should clarify, the 'owner' 'with owner access option' is the superuser--you the developer, who has all rights. You should probably read the faq's here on Access security to make sure it's set up correctly before you start this...
--Jim