Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2008
    Posts
    15

    Unanswered: Sharing a database

    Hi

    I have a database which is currently in mde status and i am having trouble when more than one person opens it to add data it comes up with an error could someone please help me solve this

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the error that is reported?
    its hard to suggest ways of resolving the problem without knowing what the error is.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2008
    Posts
    15
    Well I am not 100% sure what the error says because i have been told by a co worker think its something along the lines of locked for editing and the issue is always resolved when i go out of the database then they can happily continue adding data without any errors whatsoever

  4. #4
    Join Date
    Mar 2008
    Posts
    15
    The error reads. unable to update;currently locked

    Thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can only have one person actively editing one record at any one time
    your design ought to cater for that
    you need to decide how you want to record lock contentins to be resolved

    do you know why you are getting so many contentions
    I've found such contentions do occur, but not that often, and when they do occur they can designed out using record locking

    it maybe your table design is flawed, eg you ae storing derived information and more than one user is trying to update that att he same time.

    you may need to consider securing a table/page/record lock prior to doing the update of the affected table(s) a record lock would be safer and more multi user friendly

    the first thing to do is to work out WHY and where you are getting thes problems


    are users using the same MDE?
    do you allow you users tocreate tables on the fly.

    withut knowing the inns and outs of your application design its going to be difficult for anyone to answer your questions.... you need to do some more investigation yourself, try to work where the problems lie and act on that
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2008
    Posts
    15
    There are only two users myself and one other person using the mde file and we are both using a copy of the original mde file that was created I am pretty new to databases so maybe your right and it is a design flaw basically we are both putting data entry into a table at the same time at the moment the database properties are set to default open mode is shared, default record locking is set to no locks and the open databases using record level locking is currently ticked not sure if these are anything that would cause this error could it possibly be that the data entry form contains a ID which is the unique identifier that is set to auto number so when we are both putting data in at the same time its gettin confused and trying to update the same ID Number?

    Thank you very much for your help as i say i am quite new to designing databases so your patients is greatly appreciated.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you accessing the tables directly?
    Opening the table itself and editing records?
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2008
    Posts
    15
    We are opening a seperate copy of the mde file and we are opening a data entry form from the switchboard which is using the data table

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And the big question is...

    Do you still have the MDB file?

    You need to make sure the Record Locks is set correctly and that no-one opens the database in Exclusive mode. But you can't do this if you don't have the MDB file.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    with two users, getting table locks ....it sounds like a design problem to me
    I'm not certain yet whether its application (UI/VBA problem)
    OR
    a physical table design
    OR
    both
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2008
    Posts
    15
    Hi

    Yes i still have the MDB file and the record locks are set the same as what I mentioned aboved.
    I never get a user lock even if the other user has it open and is inputting data its always the other user that gets the error but I am not opening it exclusively.
    Basically the table where the input goes to includes
    ID-Auto Number
    with another 16 fields consisting of date fields number fields and text fields.
    There are no relationships in this database.

    Thanks

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if 'all' you have is an autonumber column and 16 other columns, then I cannot see how you are getting lock contentions... assuming that you are usign row/record level locking.

    can you identify why you are getting a lock contention
    you should only get such contentions when two or more people try to update the same row (on row locks), page & table locks are more problematical...
    so you should only get lock issues when updating, not inserting records.

    have you checked the locking on the other users copies of access. bearing in mind Unless you set thing programaticaly Access settings are unique to the local copy of Access, not the MDB... So id check each user of the Access application uses row locking and doenst' open the db exclusively
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Mar 2008
    Posts
    15
    Thank you I shall go and check the other users settings. I dont understand why the other user is getting this error when i have the database open it makes me think its something that goes wrong when i open and start adding data it is asthough we are both trying to update the same record at the same time so therefore it kicks the other user off saying sorry cant update that.

    Once again thank you

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    we are both trying to update the same record at the same time so therefore it kicks the other user off saying sorry cant update that
    Not too surprising then. Most systems are quite incapable of handling such a situation. Why are you both editing the same record at the same time?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Mar 2008
    Posts
    15
    We are not both updating the same record just the same table at the same time

Posting Permissions

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