Database with Multiple Users

  • Thread starter Thread starter smritzer
  • Start date Start date
S

smritzer

I am trying to create a database that has the potential for many users. I
recently learned how to set securities and passwords for the database, but I
still can't figure out one thing: Is there a way to have a database with
multiple users, but restrict what each person sees?
The way my database is set up now, all information is stored in a table, and
the user would fill out the form to enter their information. These users
will also have the ability to edit their information later. On both the
table and the form, however, other users can still see all of the
information, which is something I am trying to avoid. I am hoping that each
user can only see their information.
I have tried searching for different ways of doing this, but haven't come up
with anything. Maybe I'm using the wrong search terms.
Any help would be greatly appreciated.
 
smritzer said:
I am trying to create a database that has the potential for many users. I
recently learned how to set securities and passwords for the database, but
I
still can't figure out one thing: Is there a way to have a database with
multiple users, but restrict what each person sees?
The way my database is set up now, all information is stored in a table,
and
the user would fill out the form to enter their information. These users
will also have the ability to edit their information later. On both the
table and the form, however, other users can still see all of the
information, which is something I am trying to avoid. I am hoping that
each
user can only see their information.
I have tried searching for different ways of doing this, but haven't come
up
with anything. Maybe I'm using the wrong search terms.
Any help would be greatly appreciated.
Click to expand...


Are you using user-level (workgroup) security? If so, the CurrentUser()
function returns the user-name of the person who logged into the database.
You can set up your tables -- those that need this protection -- to have a
text field, UserName, and have a (possibly hidden) text box on the related
form(s) that is bound to that field and has its Default Value property set
to CurrentUser(). Also, if you create records in code, you must see to it
that the UserName field is set to the value of CurrentUser().

You would also create queries that filter records to return only those
records where UserName = CurrentUser(), and use those queries as the
recordsources of your forms and reports. That way, each user only ever sees
those records that are related to them. You should hide the tables
themselves so that the user can't get at them, and revoke the users'
permissions on the tables, giving them only permissions on the restricting
queries.

Does that clarify the approach for you?
 
smritzer said:
I am trying to create a database that has the potential for many users. I
recently learned how to set securities and passwords for the database, but
I
still can't figure out one thing: Is there a way to have a database with
multiple users, but restrict what each person sees?
The way my database is set up now, all information is stored in a table,
and
the user would fill out the form to enter their information. These users
will also have the ability to edit their information later. On both the
table and the form, however, other users can still see all of the
information, which is something I am trying to avoid. I am hoping that
each
user can only see their information.
I have tried searching for different ways of doing this, but haven't come
up
with anything. Maybe I'm using the wrong search terms.
Any help would be greatly appreciated.
Click to expand...

Yes you can do that but you would need a method to record each user's
identity against their records. One way to do this is to store the user's
network ID when the record is created. Here's some code that will return
that ID, you could call it from your form's Before Update event:

http://www.mvps.org/access/api/api0008.htm

You could then filter on the user ID field in the form's Open event.

BTW don't forget to split your app for multi-user access. Here's why:

http://www.granite.ab.ca/access/splitapp/index.htm

HTH - Keith.
www.keithwilby.co.uk
 
Back
Top