Restricted Records

How do you restrict users to select “records.” I will have several people using the same tables, queries , forms and reports, but I want a user to enter a user name password and then see only his/her records. I know how to write queries to pull select records, but I don't know how to link a user name & password to it?

Without knowing what you data is, this is one plan:

First thing you need to do is make sure your have a way of linking users to the records you wish them to see (IE attach a userid to each record they are allowed to view).

Then have the users login into the computer (thorugh a security system of your creation). Don't use the built in security of Access. As you have discovered, it is machine dependent, not DB dependent.

Then capture the Userid into another form or variable for use in your queries as a parameter the will limit the users access to those records which you assigned them to.

Good Luck

S-

each record contains a customer # which I use as the filter in the qry. How do I attach security to it now?

Hali,

First of all, I don't agree with ***sbaxter about the built-in security of Access. By default, it's machine dependent, but easily can be made DB dependent. You can set your database to one that can ONLY be opened using a particular Workgroup Information (.mdw) file. If you have made the security settings in a proper way, and the corresponding .mdw file does not exist, it's a very hard work to open the database. Even for a hacker.

Yes, as ***sbaxter said, assigning records to users requires user authentication. The logon name of the databases's current user is stored in a read-only string-type property called 'CurrentUser'. This 'CurrentUser' value is independent from the current user of Windows and IS independent from the machine. It only depends on the .mdw file that stores it.

For more on securing the database, look up Help topics like 'security', 'workgroup administrator' 'Secure your database' 'User level security wizard', etc.

Append a field to your customer table, and store the user name there to specify who can see the record. That's a fine way, but this allows only ONE user per customer record. It means if you assign a customer record to a user, then that user can only see the record, but the others cannot.

If you wish to assign more than one user to see a customer record, then you need three table, one for the customers, the other for the users of your database, and the third is to link customer records to user records (Many-to-Many relationship). In this way you will be able to assign more user record to more customer record.

BRegs,

TBÁrpi