Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Check to see if record is opened

    Ok, so I've got the front-end and back-end of the database pretty much g2g.

    Since this database is used by multiple people, which can all be on at the same time, I thought that adding in a feature to say "This record is already opened by (current user who has it opened name). Any changes to this record will not be saved until it is closed. Please try again later" if the user opens a record that is already opened.

    I have a feature already built into the db to close out the db automatically if the user is inactve for like 3 minutes, which is cool, I want that.

    But the question that I have, is actually two:

    1. Is this feature, idea, at all possible to add to the database?

    2. Is this necessary or will Access automatically just save the changes if the record is opened by someone else at the same time and they edit it?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Bound or unbound forms (I think the answer will vary depending on this)?

    What you're looking for is something called "record locking"; Google this up and see where it takes you.
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    I'm pretty sure my forms are unbound. Someone told me that Access does this on it's own but I want it to say the user name of who has the record open, and allow the other user (the one trying to access it) to have the ability to either open it as read-only or to just select "No" and close the form taking them back to the main page.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Then you have a heckoffalotta (that one's for you Rudy!) work ahead of you.
    You'll have to modify your tables to contain an "opened_by" field (or similar); which will constantly be updated.

    How many users will your app have at any one time? What are the chances of record locking occuring?

    I'd let access do it for you if I were you; there's far too much to go wrong coding it yourself

    $0.02 over
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it needen't be that much work..... you could write a record to what coul in effect become the audit log,
    person x on computer y has control of record a from table b, if changes are made update the audit log, if not....?
    id a record lock is encoutered, read the audit log to find who had row a open. send a pulse of 240vAC down the wire as a reminder to release the lock.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If this is the Task Tracker program, I'm pretty sure your data forms are bound. Personally I would add a field to the form's data table (making the field a Yes/No and calling it something to the effect: IsCheckedOut.) But then you'll need to put in some code to mark/unmark as well as look at the value of this field. Perhaps in the OnOpen event or OnLoad event of the data form write in a procedure to look at the value of that field. If it's false, set it true. If it's true, return a message box and either lock the form or close the form. Then you'll also need to set this field to false in the onClose event of the form (don't do it on a button as you'll have to remember if the user closes the entire mdb with the upper right X and doesn't click on a button.) You could also program it to write to a 2nd table with the ID field and true/false field or something to the effect of healdem's suggestion.

    This can all be a pain to code in as well as troubleshoot depending on how you have it set up. Access itself is not horribly bad with controlling updating and locking of data. When you have 3 or more users tapping into the same bound form table and on a slow network is when you "may" get into problems (again, depending on how you have it set up.) I would first think though, about the possibility of 2 or more users editing the same record. If there is a good probability of it happening on a fairly regular basis, it may be worth putting this code in. I personally don't think it's worth the time coding in and troubleshooting for what seems like a rather rare possibility for problems but it's your call (I wouldn't even put in the 3 minute auto-close code you've put in as I don't think this accomplishes a lot but again, to each his own.) In all the years of Access programming I've never had to do either and haven't had issues (although I have had some issues with user's sitting in an mdb all day whereas I solved this by making it so each user has their own frontend linking to the same data tables.)

    The ultimate solution would be to upsize the data tables to SQL Server having a much better database program to control record locking and not have to worry about any of it.
    Last edited by pkstormy; 01-07-08 at 20:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It just seems like a nacky application controlled way of reinventing the wheel. Let the DBE do the work for you otherwise you'll have unwanted record locking when someone crashes out or whatever.

    Remember, users find new and beautiful ways of breaking your application code; no matter how much error handling you stick in there
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Yeah, since I have gotten the response that Access will do it by itself, I'm not worrying about it anymore. If it comes down to it, I'm just going to move the back-end into SQL or MySQL like Paul stated and leave it alone.

    Now I'm just working on my email status issue.

    Thanks everyone!

  9. #9
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    If this is the Task Tracker program, I'm pretty sure your data forms are bound. Personally I would add a field to the form's data table (making the field a Yes/No and calling it something to the effect: IsCheckedOut.) But then you'll need to put in some code to mark/unmark as well as look at the value of this field. Perhaps in the OnOpen event or OnLoad event of the data form write in a procedure to look at the value of that field. If it's false, set it true. If it's true, return a message box and either lock the form or close the form. Then you'll also need to set this field to false in the onClose event of the form (don't do it on a button as you'll have to remember if the user closes the entire mdb with the upper right X and doesn't click on a button.) You could also program it to write to a 2nd table with the ID field and true/false field or something to the effect of healdem's suggestion.

    This can all be a pain to code in as well as troubleshoot depending on how you have it set up. Access itself is not horribly bad with controlling updating and locking of data. When you have 3 or more users tapping into the same bound form table and on a slow network is when you "may" get into problems (again, depending on how you have it set up.) I would first think though, about the possibility of 2 or more users editing the same record. If there is a good probability of it happening on a fairly regular basis, it may be worth putting this code in. I personally don't think it's worth the time coding in and troubleshooting for what seems like a rather rare possibility for problems but it's your call (I wouldn't even put in the 3 minute auto-close code you've put in as I don't think this accomplishes a lot but again, to each his own.) In all the years of Access programming I've never had to do either and haven't had issues (although I have had some issues with user's sitting in an mdb all day whereas I solved this by making it so each user has their own frontend linking to the same data tables.)

    The ultimate solution would be to upsize the data tables to SQL Server having a much better database program to control record locking and not have to worry about any of it.
    Paul, I'm going to put the front-end on each users desktop rather than just a shortcut. Since the question has been answered, as far as the locking portion, I'm not doing that and figure that auto-logout would be sufficient in the event that someone did have to edit the same record.

    After thinking about it, I don't think that people editing the same record will happen quite often, if at all, so the auto-logout feature should be more than fine. Since I figure that it wouldn't stop people from entering a NEW record if someone is already in the db, so I'm not too worried about it now.

    Just trying to work on the other issue of emailing when status is closed. Then I'm done with this thing forever!

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You're on the right track Grafixx01. I'm glad to hear that out of all that hard work you put into this program, it's being used.

    You're never really every done with it forever though

    If it's used, it'll need to be revised at some point (a month, year, or years from now.) It's more of a question on how long it's used and who's going to be the one to revise it when revision is needed (which will eventually happen if it's used.)

    If the Task Tracker is still being used a year from now, I'll bet you a six-pack and a 1/2 that it'll need "some" kind of revision.
    Last edited by pkstormy; 01-09-08 at 17:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Aug 2006
    Posts
    559
    Yeah, it'll probably be needed to be 'revised'. But you know what, I am hoping that I won't be here at that time and hopefully this new job comes through. So if I leave, hmm... They won't know what I'm doing as far as a 1million dollar building renovation and all the IT equipment, nor the computers I have in locations as servers...Oh well!

Posting Permissions

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