Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    2

    Exclamation Answered: Cannot update records; locked in Access 2013 shared database

    Hello: I apologize ahead if this is a repeated topic. I have read through some old threads and did not seem to match the problem exactly what I am having or there is probably no solution.

    I have a shared database build in 2007/2013 Access (built in 2007 then edited in 2013) sitting on a shared drive.
    Database is split into back-end and front end.
    Back-end is sitting on the shared drive. Front-end appropriate linked to the back-end folder.
    ACCDE is created after splitting the database and now each machine has its own copy of ACCDE sitting on desktop.
    I have set the Records level setting to "No Locks" in Access Options and also uncheck the "Open Databases by using record-level locking".
    Also on the form, records have NO locks.

    The problem: no two people can update the forms by selecting a same field EVEN THOUGH WE ARE NOT UPDATING THE SAME RECORD. My form has fields with multi select list boxes to select.

    Is it due to fact because I have multi select list boxes?

    Can someone give me any solution? I am not a programmer and I do not know codes but I am capable of applying codes if someone can provide. I truly appreciate any suggestions.

  2. Best Answer
    Posted by Missinglinq

    "
    Quote Originally Posted by msann View Post

    ... My form has fields with multi select list boxes to select...
    It's very important to get your terms correct, when asking for help...per your attachment, you do not have 'multi select list boxes,' but rather multi-select Comboboxes, and multi-select Comboboxes are always associated with MultiValued Fields. It's important to make the distinction between Listboxes and Comboboxes, because the term 'multivalued' means different things, depending on the Control type you're talking about!

    Quote Originally Posted by msann View Post

    ...Is it due to fact because I have multi select list boxes...


    MultiValued Fields are shunned, by most experienced developers, because they violate one of the cardinal rules of Relational Databases, sometimes called the 'atomic rule,' which states, basically, 'one piece of data in one Field.'

    The Access Gnomes handle this by actually storing the data in the MultiValued Fields not in the Table they appear in, but in a separate, system Table. Because of this, almost everything you do, involving MultiValued Fields, requires different/special handling, unlike that needed for other Access Fields. While I haven't seen this particular problem associated with MultiValued Fields (and I've spent a good 6-8 hours a day, here and on other Access forums, for the past 10 years!) my guess would be that 'yes,' your problem is probably associated with the use of these MultiValued Fields. Two users accessing the same Control (albeit on different Records) are accessing that separate system Table, at the same time, and I suspect that herein lies the crux of the problem.

    Why not

    1. Copy your Form
    2. On that Form simply delete the offending Controls
    3. Import a copy of the new Form into a second copy of the Front End
    4. Check, with you and someone else using the imported copy, to see if you can reproduce the problem, or if it is now fixed.

    Perhaps you can explain why you need to use MultiValued Fields...perhaps we can offer a better alternative.

    Linq ;0)>"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    This makes no sense. You split it correctly, so neither should affect the other person.
    NOW,
    its possible if you both have a large datasheet open, (rather than edit 1 record) that may do it.

    You should display the datasheet as snapshot.

  4. #3
    Join Date
    Dec 2012
    Posts
    2

    Exclamation

    Quote Originally Posted by ranman256 View Post
    This makes no sense. You split it correctly, so neither should affect the other person.
    NOW,
    its possible if you both have a large datasheet open, (rather than edit 1 record) that may do it.

    You should display the datasheet as snapshot.

    That was also my assumption. I have build a shared database (back-end on the server and front end on each computer) before with occasional problem (since I did not create an ACCDE version for everyone but they are pulling the same front end from the server). But with this new database, I thought I am doing the full-proof by installing individual ACCDE version on each desk-top. I am editing individual record at a time and no one is editing the same record. The fields have list box in drop down as shown in the attachment. Could this be the one creating a problem here? The minute two people select the same field, both records get locked until the first one got in save and close the record. On the form, it is set to have No Locks.
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  5. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by msann View Post

    ... My form has fields with multi select list boxes to select...
    It's very important to get your terms correct, when asking for help...per your attachment, you do not have 'multi select list boxes,' but rather multi-select Comboboxes, and multi-select Comboboxes are always associated with MultiValued Fields. It's important to make the distinction between Listboxes and Comboboxes, because the term 'multivalued' means different things, depending on the Control type you're talking about!

    Quote Originally Posted by msann View Post

    ...Is it due to fact because I have multi select list boxes...


    MultiValued Fields are shunned, by most experienced developers, because they violate one of the cardinal rules of Relational Databases, sometimes called the 'atomic rule,' which states, basically, 'one piece of data in one Field.'

    The Access Gnomes handle this by actually storing the data in the MultiValued Fields not in the Table they appear in, but in a separate, system Table. Because of this, almost everything you do, involving MultiValued Fields, requires different/special handling, unlike that needed for other Access Fields. While I haven't seen this particular problem associated with MultiValued Fields (and I've spent a good 6-8 hours a day, here and on other Access forums, for the past 10 years!) my guess would be that 'yes,' your problem is probably associated with the use of these MultiValued Fields. Two users accessing the same Control (albeit on different Records) are accessing that separate system Table, at the same time, and I suspect that herein lies the crux of the problem.

    Why not

    1. Copy your Form
    2. On that Form simply delete the offending Controls
    3. Import a copy of the new Form into a second copy of the Front End
    4. Check, with you and someone else using the imported copy, to see if you can reproduce the problem, or if it is now fixed.

    Perhaps you can explain why you need to use MultiValued Fields...perhaps we can offer a better alternative.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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