Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concurrent Users (Access 9.0.2720)

    I have developed a database for use by several users. I don't know where to begin to set this up for concurrent users. Where do I begin? Thanks!!

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

    Re: Concurrent Users (Access 9.0.2720)

    What do you mean by concurrent users? Are you talking about several people on a network using the application? If so, the best way is to split the database into a front end (all user interface elements, queries, code , etc.) and a back end (data tables) and put the back end on the server. Each user should have a copy of the front end, with links to the tables in the back end, on his or her own machine. Access is inherently multiuser, but you may find that some design features will need to be changed to work best with a multiuser environment.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concurrent Users (Access 9.0.2720)

    Charlotte -- I have read on several different posts about how splitting the database is useful for multiuser environments. Can you please explain why that is true?

    I, too, am trying to determine a good way to make my database multi-user friendly. It is an inventory control system, and my key concern is when one user goes to move inventory from one location to another, that another user attempts the same. In my situation, however, the form to move inventory has a bunch of unbound controls because the original record isn't changed to reflect the new location; rather, a new entry is created for the new location, and the original record's quantity changes (and a "last modified" date field is updated).

    Given that the user is changing unbound fields, it does not lock the current record in "edit" mode.... And therein lies my problem. At least with a recordset I could specifically lock the record with ".edit" (although I wouldn't know how to determine ahead of time if the record is locked before I try to lock it), but on a form I don't know how to lock the record driving the form until I'm done...

    Nor do I know how to "determine" in code, whether or not the record on the current form is already locked by another user...

    Any ideas?
    ..dane

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concurrent Users (Access 9.0.2720)

    Dane

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

    Splitting your Microsoft Access MDB into a front end and back end

    HTH

    John

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concurrent Users (Access 9.0.2720)

    Thanks John -- I'll go read that. Looks like a lot of good information!

    Any ideas on my other questions (not related to splitting)?

    thanks again,
    ..dane

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concurrent Users (Access 9.0.2720)

    Dane

    Do a forum search on record locking, here is one <post#=1917>post 1917</post#>

    Check out: http://advisor.com/doc/13043

    HTH

    John

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

    Re: Concurrent Users (Access 9.0.2720)

    In addition to the suggestions you've received, I'm a little puzzled by your statement
    <hr> ... the original record isn't changed to reflect the new location; rather, a new entry is created for the new location, and the original record's quantity changes (and a "last modified" date field is updated).<hr>
    I believe you are still changing the original record, in which case you need to be concerned about two people moving things from the original record at the same time and getting tangled up. Tjat's what record or page locking is all about - so whatever your default locking settings are for your database will get applied when you change the quantity in the original record. You might want to look at the thread which begins with <post#=346636>post 346636</post#> as it is looking at a very similar question to yours. You might also find our tutorial on splitting databases - it contains some procedural steps as well.
    Wendell

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Concurrent Users (Access 9.0.2720)

    You could institute you own locking mechanism. This would require adding a field to the record to show who has the record locked. You would need to "lock" the record when you start your transaction, then "unlock" it (by clearing that field) when you are done. This would require some work, however.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concurrent Users (Access 9.0.2720)

    Hi Mark

    Is this something you have done? Is there a sample DB available?

    John

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Concurrent Users (Access 9.0.2720)

    The last time I did something like this, I was only concerned about locking the records in 1 table. So, I created another table that had 2 fields in it: an ID field (I used the machine ID) and the PK of the record I wanted to lock. Depending on the situation, I would lock a record by checking to see if that PK was already in my locking table for another machine. If not, then I updated the current machine's record with this PK. Often I did this in a form's OnDirty event, then cleared the lock in the AfterUpdate event. It seems to work pretty well, eliminating the conflicts we had been having.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Concurrent Users (Access 9.0.2720)

    That is essentially the method that's been used since Access 2.0 to simulate recordlocking in Access. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concurrent Users (Access 9.0.2720)

    Thanks to ALL for such response to my questions!

    I've been soldering all morning (I'm an EE by degree, Access developer by hobby) and haven't had a chance to read all of the references posted in reply to my questions.

    However, to fill in a few questions real quick; Yes, I do need to update the existing record with the new quantity, good clarification. I suppose what I meant to indicate was that since nothing in the record is actually updated _by the form_, then I don't get to benefit by any of Access's natural record locking mechanisms. If two people open the same part to distribute it out to someone, then none of them have locked the record. They fill in their "unbound" fields on where it's going, and then hit "go" and code does the rest... create a new entry, update this entry, locate that entry, and so on and so forth...

    Walking out to my car last night I did think about implimenting my own locking mechanism such that when a user opened the form to disperse the part, it updated the record with a field. I then thought "what happens if their computer crashes?" The field is then permanantly locked, so I am thinking about implimenting a field update on a periodic basis... if the field isn't changed for a period of time, then someone else can have it.. a "timestamp" would be great, except of course that I cannot guarantee what a user's local clock value might be set to (a user may have JUST opened the reord, but since their clock is wrong, another user thinks they can open it since it hasn't been touched in "xxx" amount of time)

    One last question before I head off to a meeting....

    If a record _IS_ in fact locked, how do I determine that in code? If by chance two people "detect" that they can update the field at the same time, obviously ONE of them will get there first, and the other will be locked out. Rather than Access popping up some message about it (and therefore totally confusing the user who is likely not computer savy), I'd like to detect that. Haven't found out how, yet, though.. [img]/forums/images/smilies/sad.gif[/img]

    Thanks so much,
    ..dane

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Concurrent Users (Access 9.0.2720)

    This was really the first time I've had to worry about record locking in an Access app, and the technique seemed to work quite well once I had found all the places the record could get updated! In this app, a user could be talking to a client (with the client info displayed and changes made) and then turn the call over to someone else who would display the client info before the 1st person had closed out. Having them shout "OK, I'm out!" didn't seem like a viable alternative!<g>
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Concurrent Users (Access 9.0.2720)

    <hr>Having them shout "OK, I'm out!" didn't seem like a viable alternative!<hr>
    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> Maybe not, but it would certainly make for an interesting work environment! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

Posting Permissions

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