Need Multiple Users/Permissions Suggestions

[shm135]

Hi,

I have a database that will be shared/accessed by about 100 people.
There is only one table, but many fields in the table. I need
suggestions for how to implement the following:

1.) I need the contents of the main table to be filtered based on the
user that is using it. Is there a way to make only specific records
updateable/viewable based on the person who is using the database? How
can I eliminate the possibility of a user seeing records that he does
not need to see? For example, if my name is John Smith- I should only
be able to view/edit the records whose point of contact is listed as
John Smith.

2.) How can multiple users access the database at one time and update
information? For example, if I am updating my records in the table
(using a form), how can my partner update their records, while
applying both our updates?

3.) The database needs to be accessible from all over the place- so it
will likely be placed on a network/sharepoint site somewhere. Any
suggestions for how to speed up working in the database?

I can save in either Access 2003 .mdb or Access 2007 .accdb - but will
be using Access 2007 to make the changes. Please share your thoughts-
I'd appreciate it.

 

[Tom van Stiphout]

See comments in-line.

-Tom.
Microsoft Access MVP

Hi,

I have a database that will be shared/accessed by about 100 people.
There is only one table, but many fields in the table.

Tom: That sounds like a really bad database design. This will likely
come around and bite you.
100 users is quite a bit for Access, especially if they are concurrent
users. I hope you are a seasoned developer.

I need
suggestions for how to implement the following:

1.) I need the contents of the main table to be filtered based on the
user that is using it. Is there a way to make only specific records
updateable/viewable based on the person who is using the database? How
can I eliminate the possibility of a user seeing records that he does
not need to see? For example, if my name is John Smith- I should only
be able to view/edit the records whose point of contact is listed as
John Smith.

Tom: You can ask Windows who is logged in:
http://www.mvps.org/access/api/api0008.htm
Then you can query the table using this information. For example:
select * from myTable
where myUserName = fOSUserName()

2.) How can multiple users access the database at one time and update
information? For example, if I am updating my records in the table
(using a form), how can my partner update their records, while
applying both our updates?

Tom: That's the magic of a multi-user database.
Note that locked records are par for the course in multi-user
databases, so your error handler needs to be able to handle those
gracefully (e.g. go to sleep for 250 msec and try again).

3.) The database needs to be accessible from all over the place- so it
will likely be placed on a network/sharepoint site somewhere. Any
suggestions for how to speed up working in the database?

Tom: Here are many tips:
http://www.granite.ab.ca/access/performancefaq.htm
You also need to split your db in front-end and back-end.

 

[shm135]

Thanks for the response....please see my comments

I am not an amazing developer but have built some complicated
databases before. Just nothing requiring more than one user....I was
hoping someone could help me figure out the best ways to do things.
There will be about 100 users, but typically, a max of 5 at once will
be using it at once....I'm rethinking the layout...

There are three categories.
Each user will be designated to a category
Each category has its own set of questions
Each user should only be able to edit/view/add one set of answers
....................................................................................................

Tom: You can ask Windows who is logged in:http://www.mvps.org/access/api/api0008.htm
Then you can query the table using this information. For example:
select * from myTable
  where myUserName = fOSUserName()

...................................................
SAM: In this case- what can stop the user from going beyond the query
and opening the myTable table to view everyone else's responses? Any
way to disable that? Also, this can not be based on Windows login
credentials...is there any way to manage the database login
credentials from within the database?
...................................................

Tom: That's the magic of a multi-user database.
Note that locked records are par for the course in multi-user
databases, so your error handler needs to be able to handle those
gracefully (e.g. go to sleep for 250 msec and try again).

...................................................
SAM: There will probably be about 5 user simultaneously...but no user
will edit the same record at the same time....they might be altering
information in the same table at once, but the records being edited
will be different
...................................................

Tom: Here are many tips:http://www.granite.ab.ca/access/performancefaq.htm
You also need to split your db in front-end and back-end.

...................................................
SAM: Great link thanks...


I am wondering, instead of requiring users to use MS Access to answer
the questions that apply to them- is there any other way to get their
responses to the questions and easily gather their responses in Access
to be further analyzed? I think this will remove any security issues?
I've seen that you can send forms via email- that might work...let me
know if there is anything else

 

[Tom van Stiphout]

See comments in-line.

-Tom.
Microsoft Access MVP

Thanks for the response....please see my comments

I am not an amazing developer but have built some complicated
databases before. Just nothing requiring more than one user....I was
hoping someone could help me figure out the best ways to do things.
There will be about 100 users, but typically, a max of 5 at once will
be using it at once....I'm rethinking the layout...

There are three categories.
Each user will be designated to a category
Each category has its own set of questions
Each user should only be able to edit/view/add one set of answers
..................................................................................................

..................................................
SAM: In this case- what can stop the user from going beyond the query
and opening the myTable table to view everyone else's responses? Any
way to disable that? Also, this can not be based on Windows login
credentials...is there any way to manage the database login
credentials from within the database?

Tom: You can hide the Navigation Pane. You can hide tables. Mostly it
just takes presenting a well-rounded UI and users won't stray too far
from it.

..................................................
..................................................
SAM: There will probably be about 5 user simultaneously...but no user
will edit the same record at the same time....they might be altering
information in the same table at once, but the records being edited
will be different

Tom: Pfft, that's a lot better than 100. With optimistic locking on,
you will hardly ever have a collision.

..................................................

..................................................
SAM: Great link thanks...


I am wondering, instead of requiring users to use MS Access to answer
the questions that apply to them- is there any other way to get their
responses to the questions and easily gather their responses in Access
to be further analyzed? I think this will remove any security issues?
I've seen that you can send forms via email- that might work...let me
know if there is anything else

I wouldn't go that far. This email feature is new for 2007 and may
need some polish before it's ready to rely a mission-critical
application on it. But by all means try it out to see how well it
works for you.

 

[Cliff]

See comments in-line.

-Tom.
Microsoft Access MVP


Tom: That sounds like a really bad database design. This will likely
come around and bite you.
100 users is quite a bit for Access, especially if they are concurrent
users. I hope you are a seasoned developer.

Tom: You can ask Windows who is logged in:
http://www.mvps.org/access/api/api0008.htm
Then you can query the table using this information. For example:
select * from myTable
where myUserName = fOSUserName()

Tom: That's the magic of a multi-user database.
Note that locked records are par for the course in multi-user
databases, so your error handler needs to be able to handle those
gracefully (e.g. go to sleep for 250 msec and try again).

Tom: Here are many tips:
http://www.granite.ab.ca/access/performancefaq.htm
You also need to split your db in front-end and back-end.

Hi,

Regarding each users only seeing their own records; what about "users"
who have a manager role who need to see all records? OR an account to
run reports/summaries on?

I agree with Tom that the one table scenario is a really bad design.
In addition, the record locking aspect of Access and only one table you
are almost guaranteed of user collisions.

Also, just because there's only 5 users now doesn't mean that might not
be increased in the future.

Have you considered using a different database product to meet your
requirements? Some are even free


Cliff

 

[shm135]

Thank you Tom and Cliff for your input...

I'm not sure what other database products out there will work for me.
I need something that can be secured- requiring a login of some sort.
I need something that can ask the user a question, and ask other
questions based on their answers. ie- if answer 1 is yes than ask
question 1a, else ask question 1b. I need something that can be
accessed from anywhere, by multiple people simultaneously- while still
allowing me to make changes as needed. Do you think Access is not the
best tool for this?

Any suggestions for how to enable a login prompt upon opening the
database, without incorporating Windows login credentials? I'd like to
manage my own list of users and groups.

Thanks