Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi-user environment (97)

    hi all!
    i am currently working on a database for my company that keeps track of job numbers taken out for each job we do. it will be kept on our network and essentialy accessible by everyone (up to 60 users - probably not more than 2 or 3 at any one time), hopefully with different rights.

    there are 3 types of users: the most basic are the general employees - they will only be searching the database and viewing results, printing reports, etc.

    the next group are the principals (owners of the company). they will be able to create new records (using forms i created). they will not be able to edit records once they create them. the reason for this is that some are not very computer literate and we don't want them messing things up...
    they will additionaly have the rights of the employees.

    the third group (IT personnell, myself, and secretaries) will have all options available and can edit records, etc.

    i was planning on having all options on a main menu form, and depending on which group of users the employee was in, only show certain options.

    the owners of the company do not want to use passwords, so the solution we came up with was to have 3 different front ends (with only the main menu changed), one back end (for tables), and set up a shortcut on every user's desktop pointing to the corresponding front end they were permitted to use (eliminating all rights to the other two front ends).

    the biggest problem i see is that if one of the principals is entering a new job number, i want to only allow viewing of records to everyone else, therefore eliminating duplicating job numbers. i do NOT want to resort to locking them out of the db completely, i only want to give them limited options. i am not sure what i need to do from this point to get this to work properly.

    any help would be appreciated

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    I am unclear whether you have rejected the idea of using user-level security ("... don't want to use passwords..."), but if you have, then you might want to reconsider. It's the key to allowing certain groups of people permission to edit the tables, while denying other users such permission.

    There is a way around the password requirement while using user-level security. Since you are prepared to create a shortcut for every user, you could encode the loginID and password into the shortcut, thus hiding it from the user. They wouldn't even be aware that a userID and password were being used.

    This approach would also allow you to use just one frontend, which would likely be easier to maintain over the long run.

    Just my $0.02
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    thanks for your reply...
    what i have found is a little function in "Total Access Components" (an add-on to access) that will find the username on the particular system that the user is logged in to (using Novell Network). i have a table for employees, which also includes the username each employee has. i also included what level of access each employee should have (user, principal, and administrator). my main menu checks all this and only displays certain menu choices depending on which group the user resides. i think this will solve my problem of user-level access..

    the problem i have to figure out now is how to only allow one person to be entering new job numbers, and only allow other users to view job numbers and such while the first person is connected. doesn't seem that hard, but i haven't a clue on what to do. any further help would be appreciated.

    thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi-user environment (97)

    You are reinventing the wheel. Access security *would* allow you to handle this. Save yourself a lot of <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> and consider it.

    Our commercial apps are secured, but the users never notice because the user they log in as does not have a password and the user name and MDW reference are in the shortcut. Give your boss a shortcut that includes *his* username and password as well as the MDW reference.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    thanks charlotte
    i will assume you referencing the issue of "who can see what". it seems that what you are recommending could very well work. however, i have no clue on how to do such a thing, and what i have set up works as of now. would you still suggest converting what i have done over to your method? remember, i would have to learn how to do what you are proposing.

    the real issue is only allowing one user to enter records at a time.. not sure how to accomplish this. i don't know exactly how access handles this on its own, perhaps you can help

    thanks

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    I agree with Charlotte that user-level security is the way to go. YOu should download the article from here http://support.microsoft.com/default.aspx?...b;EN-CA;q165009
    and read it thoroughly and follow every step exactly.

    Not quite sure what you mean by "...one user to enter records **at a time**".

    Access' user-level security will prevent anybody except designated users from editing the data *at all*. So you could designate one or more people to add/edit the records. But if two people have been given permission to add/edit the data, then there is nothing to prevent both of them from doing so simultaneously. Is that what you mean?

    The way it works with user level security is that users simply cannot change records in tables for which they haven't been granted permission to change.

    Edited by Charlotte to activate link
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    thanks for your help..

    i printed that article out and i will read it thoroughly before i decide what i will do. thanks again for your help

  8. #8
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    hi,
    i started to read the article you sent me to... and i must say that most of it is way over my head. i think if i jump in with both feet, i would surely drown. perhaps you might have some insight that may make this matter a bit easier to comprehend...

    i do worry about your statement that you made: "But if two people have been given permission to add/edit the data, then there is nothing to prevent both of them from doing so simultaneously". this is exactly what i mean and really the only thing i need to prevent. other users viewing certain data is not much of a concern.

    thanks for helping someone with no security experience.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Multi-user environment (97)

    What you are worrying about is what is called record (or page in 97) locking, so that when someone else starts to edit a record, other people are locked out. That is an option that can be set at the database level where you can specify "no locks", "edited records" or "all records" - check out the help in 97 on locking. In nearly all the databases we design, we use "no locks" as the probability of a conflict is relatively low - and we are using SQL Server in most cases.

    Another option is to open the database exclusively for those people who edit, but that is fairly strict way of enforcing the editing of records. Security, which you've been exploring, simply gives some people the ability to prevent some people from editing, or even viewing, some records, forms, reports, etc. Hope this helps.
    Wendell

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    I think something is getting lost in the translation, here. I re-read your original post, and it looks to me like you need to prevent two people from creating the same new ID number. That is a different concept than preventing two people from editing the database simultaneously.

    If you could elaborate on your business requirements, I might be able to provide some additional help. Perhaps describe the "ideal workflow" that you would like to achieve -- with and without multiple "editing" people logged into the database simultaneously.

    Maybe all you need is the ability to create a new ID number and prevent anybody from the originator from editing that ID number???
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multi-user environment (97)

    Even if two (or more) people have permissions to add/edit data, they still can't *save* simultaneously. One of them is going to get locked out by the other. Depending on how you set up your locking scheme (which doesn't have anything directly to do with security), you can keep arrange things so that while one person is editing a record, no one else can do anything but view it. Is that what you are trying to do? Perhaps if you explain more about what you want to accomplish, you can get more applicable help.
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    ok,
    i guess i have gotten off track a bit.. i will try to start over. what we have is a database keeping track of the job numbers we take out for new jobs (perhaps like 02005A). we have 5 principals (owners) who enter these job numbers. they must all be permitted to enter information, and i need to find out how to prevent two people from entering the same job number. right now, i have a form for entering these job numbers. the form goes out and finds the last used number, adds one to the number and automatically inserts that number into the job number field. this is working very well if only one person is entering information. there is no more than 500 or so new job numbers in any given year, so really the chances of two being created at the same time is quite low. however, there still is a chance, and i need to make sure it doesn't happen.

    here's what happens as of now... if two people have the db open to add a number, both instances will see the most recent number, add 1 to it and give the same number. if one finishes and clicks ok to enter the job number before the other finishes (at least by a few seconds) when the second accepts the data, it will say the job number is already in use.

    if they click ok at the same time, there will be two instances of the same number in the table. if i set that field to the primary key, it will add whichever number is added first and ignore the second (without any warnings). both instances are not good. i have also tested record locks to all the different settings, and i am not getting anywhere.

    any more help would be greatly appreciated and let me know if you need more info. i could also probably send you the form i am currently using.

    thanks again

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Multi-user environment (97)

    Why don't you keep the Job number in another table (say a table called JobNumberControl, not the table you currently go to get the job number).
    That way when a user needs the next job number it just goes to that table, adds 1 to the number and writes it back to that table, this is then used as the job number.
    Cheers,
    Pat

  14. #14
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    Having a unique index on the job number column should prevent anyone from adding the same number more than once. Your statement

    if they click ok at the same time, there will be two instances of the same number in the table. if i set that field to the primary key, it will add whichever number is added first and ignore the second (without any warnings

    doesn't make sense to me. When I try and add a duplicate value to a column that has a unique index I get an error message. At this point you should be able to capture the error, prompt the user if they want to cancel the change or re-search the table to find the largest number, add one to it, and use the new number as the job number. The only thing I'm not certain of is what the error number is that you need to trap this error.

  15. #15
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-user environment (97)

    You said "if two people have the db open to add a number, both instances will see the most recent number, add 1 to it and give the same number. if one finishes and clicks ok to enter the job number before the other finishes (at least by a few seconds) when the second accepts the data, it will say the job number is already in use."

    This seems to be the problem. The job number is generated for the data entry and not immediately posted to allow the second user to obtain the next number in the sequence. You can use the before insert event to populate the job num and post the record.

    A better solution is or leave the Jobnum blank until post and then get the next number in the sequence. Use the before update event to check if the job num is populated and if not populated grab the next number in the sequence.

    This will allow you to maintain a sequence without any gaps and without having two users recording the same job number. As one of the other posts said your sequence of job numbers probably should be in a seperate table, just to make life easier for you.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •