MultiUser Environment - Allow certain list data to appear

[TJones76]

Afternoon...
Helppppp.....I have a database that I need to make available to about 36 users. How do I create it so that each user only has access to their data, not able to view any other person's information??
Also, on the data entry form, I want the name of the user, to appear in the Name field automatically (i'm assuming that would be based on their log in and password that would trigger the event)
Basically the idea is that each person has their own individual data entry form and reports.. but technically there is only one form, one table it's based on, and one report restricted to display only that user's info.

Any suggestions?? I need to do this in the next week or so...deadlines...ughh

-Tjones76

 

[johnwm]

Assuming you're using some flavour of SQL (obvious really with 36 users!) lock the table to admin only and build Views, each passworded to the user.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[TJones76]

Thank you thank you for replying so fast...
Problem is.. I'm using Access 97 (company standard) and I have zero experience with setting up a multiuser database. This database will be housed on a network drive for access by these users.

I've got a form with two subforms attached.. everything works great.. i've got the two subforms directly tied to the main form by the User and Date. But now the trickiest part.... to make it so that one form supports many users and they all cannot view one another's info. Is this even possible??


-Tjones76

 

[johnwm]

My understanding is that Access 97 is primarily a desktop database application, and not really designed for the heavy usage that you may expect from 36 users on a shared db.

If you are constrained to try a shared db for 36 users then you can load each users form from a parameter query based on username rather than on the table as a whole. You will probably get more detailed help in one of the Access forums, most of which are listed in the 'Related Forums' panel on the right of this page.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[JeremyNYC]

Properly designed, an Access DB should be able to handle 36 users, as long as they're not pushing tons of data back and forth. But if you're new to this, it's going to mean a LOT of learning for you.

36 users _is_ a lot for Access, and if there's any chance that they will all be in there constantly, moving a lot of data around, or that the user base will grow significantly (be pessimistic about this--if you design a good database other people will want to use it), you should work hard to gt your boss to understand that Access could be outclassed by this problem.

Here are some of the things you will have to do to get this to work in Access. If you don't know what some of them are, check out the Access forums here, or search the help files, or seach groups.google.com, in the group comp.databases.ms-access. You should be able to find just about all of the answers you need there. If you still have questions, I'd suggest checking out the Access forums and posting the questions there.

-Split the database into a front end and a back end.
-Implement user-level security. (I've got the MS Security FAQ on my site, along with some of my own stuff about Access security on my site.)
-Store the user's name in the table and use CurrentUser to return the name of the user once the person is logged on to display that in the main form. Then use a parameter in the recordsource for the subform pointing to that field on the main form, or to currentUser().
-Design your tables and queries very carefully--indexing the fields that will be filtered and sorted on, but not the others, and nailing the normalization (see Paul Litwin's article in the Database Design link on my site), and pulling across only the data you need to pull across.
-Consider using unbound forms to minimize chances for conflicts and corruption.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[TJones76]

You guys are wonderful! Thank you for the detailed advice, I'll give the suggestions a try.

I don't believe it will be a problem with the # of users since they'll be going in at different times and only once a day... but we will see.

Thanks again,
Tiffany

-Tjones76