locking tables

[rkmorrow]

rkmorrow (MIS) Feb 15, 2002
I have an access97 database that was created by a company for a smart card system.

The database holds client information: name, ss#, home address. There are counselors for the clients that also have been input into the database. The counselors are concerned that their own information can be read by anyone (other counselors)that can access the databse.

I don't know that much about databases, but the forms have a field for inputting a code number on the accounts. It seems that I should be able to apply a code number, say 99 on the counselors accounts and lock them from anyone viewing those accounts except administrators.

Is there an easy way to do this?

Thanks for any input

rkmorrow

 

[tunsarod]

Is the consultant information integral with the client info, that is all combined into one table, or do you have the consultant info in a separate table? i.e. Normalised.

How is the client and related consultant information displayed? A single form, a mainform/subform combination or two separate forms?

What is the relationship between:

clients and consultants

1 to many
1 to 1
many to many
many to 1

My guess is that logically consultants have many clients while clients have only one consultant, but this could be quite wrong.

Rod

 

[rkmorrow]

Thanks for the reply rod,

The couselors are in the same database as the clients without any distinction(not the way I would have done it).

It is all displayed in a single form.

There is a seperate additional table with the couselors name and counputer name, that allows the receptionist (who looks at one of the search forms)to notify a particular counselor that a client is here for an appointment.

The counselors can pull up the search form and look at their own data, another counselors, or a client's. It is also a 1-1 relationship, if i am understanding correctly, there are no groups of clients associated with any single counselor.

Thanks for any input.

rkmorrow

 

[tunsarod]

I wrote a lengthy message last night but somehow it seems to have got lost and hasn't arrive on this thread so I'll try to repeat the main points.

I assume that your counsellors see many clients over time, albeit one at a time. The relationship between counsellors and clients would therefore be 1 to Many. i.e. I counsellor is associated with Many clients.

If the only reference to a counsellor would be his name entered into the client record of those clients he counselled then a single file system would certainly work but this would mean repeated entry of each counsellor's name over time - but what the heck?

Even with such a limited amount of counsellor information I would tend toward a separate table for counsellors names. That would require a Primary Key field in counsellors say CounsellorID with a matching field as a Foreign Key field in the CLIENTS table.

A textBox on the Clients form called txtCounsellorName with a countrol source of:


=DLookup("CounsellorName","COUNSELLORS","[CounsellorID] = [Forms]![ClientsForm]![CounsellorID]&quot


would show the name of a counsellor if the Ccounsellor's ID field was filled.

This all pre-supposes two tables; CLIENTS and COUNSELLORS.


In order for the receptionist to call up a client's record and identify the client's counsellor the receptionist needs access to all client records. Clearly the ClientsForm needs to get its records from the CLIENTS table albeit you do not need to provide the recepionist with a view of all fields.

For example a memo field for counsellors to records client notes, if one existed, need not be visible to a receptionist.

Conversely a counsellor accessing a client record may need to see all the relevant data about the client.

The solution is to provide the receptionist and the counsellor with different forms. The receptionist's Client browse form should have its Control Source set to the table CLIENTS and only relevant fields visible, or to a Query which accesses selected fields for ALL Client records.

The counsellor should have a Client browse form which accesses records via a Query which limits the records available to those only which have the counsellor's ID in the CounsellorID Foreign Key field.

Assuming the Counsellors Client view form is called CnslrsClientForm then the Query's SQL should look something like:


SELECT * FROM CLIENTS WHERE [CounsellorID] = [Forms]![CnslClientForm]![txtCounsellorID];


For this to work the CnslrClientView form should have a text box in its header called txtCounsellorID and a command button which has in its click event the line:
Me.Form.Requery

The following Form properties should also be set:

AllowEdits = yes
AllowDeletions = No
AllowAdditions = No
AllowFilters = No
Data Entry = No

By using a query to source records for the counsellor's CLientView form you prevent the counsellor from accessing records other than those with which he is associated.

The CounsellorID value ought NOT be an autonumber sequenced field but should be at least four digits long and issued by random creation.

You cannot use filters to restrict records because the counsellor could too easily switch of the filter to gain access to unrelated client records.

You should alos take steps to prevent counsellors from accessing the database window. Creating an MDE file and hiding the CLIENTS table, are just two things you might consider.

Hope this all helps.

Rod