Prev: Office 2007
Next: Custom Sequential Numbering
From: smritzer on 18 Jan 2010 10:44
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.
From: Dirk Goldgar on 18 Jan 2010 11:10
"smritzer" <smritzer(a)discussions.microsoft.com> wrote in message
news:685F8FA4-B940-4F3D-8B8C-08239A8270F9(a)microsoft.com...
>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.


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?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: Keith Wilby on 18 Jan 2010 11:33
"smritzer" <smritzer(a)discussions.microsoft.com> wrote in message
news:685F8FA4-B940-4F3D-8B8C-08239A8270F9(a)microsoft.com...
>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.

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

From: smritzer on 19 Jan 2010 11:28
Thank you for your assistance, I will try these things out.


 | 
Pages: 1
Prev: Office 2007
Next: Custom Sequential Numbering