Results 1 to 13 of 13

Thread: Locked Table

  1. #1
    Join Date
    Nov 2005
    Posts
    13

    Unanswered: Locked Table

    I created a MS Access data base that is working great but when mulitiple people try to access it at the same time, they're getting messages saying that the table is locked and the db will not open. The first person to use the table has no problems but everybody else has to wait until the first person closes the db before they can open it up and edit it. I've played around with all combinations of permissions but the problem persists. Any thoughts?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Lightbulb Multiple people accessing the same table.

    Others might have more ideas but here are some I can think of (I wasn't quite sure what you were doing - needed more info):

    Check your code - you should be able to have 4-5 MSAccess users in the same db at the same time. One of the times I've seen this type of error is when doing a make-table and having a report open based on that table. If another user had that report open, the table was locked until the other user closed the report. You can't make a table if another user has that table open in some way and maybe something in your startup code does something similar to this which would give you an error (is this on a make-table from a query) - As a last resort, you can always create a table based on the user's login (you can get the user's login via vba) and perhaps set the rowsource of your form/report to this table on opening. Then update back to the main table if need be.

    Seems to me though that something is going on but I can't think of why right now. Have you tried copying all the tables/queries/forms/reports/modules/macros to another blank db? Is your code debugged/compiled?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2005
    Posts
    13

    Thanks for the tip

    Paul,

    Thanks for the tip. Sorry I didn't give more info. I am very new to Access so I'm learning lots everyday.

    My db consists of a query from a table of first and last names on our server. I have it set up in a "make a table query" such that it pulls only the first and last names from the parent xls table on our server. Once I've got all the names I have built an additional, non-linked table that represents yes/no answers to people from the query. Hope this makes sense. My bottomline result is this: A form that has all the names of my people with yes/no check marks next to them. Each person opens up the database and selects yes/no to each of the 10 questions. In addition I have created a single report that simply shows ALL names and ALL yes/no answers. The locked table warning pops up when the second user opens up the db regardless if the first person has a report or form open. In otherwords, the first person can simply only have the switchboard open and this will prevent all others from opening the db.

    When you write "Check your code" I really don't know what you're talking about. Hmm

    KML

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off I'd check the default record locking - depending on your version of Access (A2000 on) edited record locking, and / or default opening mode : shared

    I'd also check if any of your forms or reports are changing, or if your users are making changes to the design (not the data). Changes to the design cannot be made on a shared database.

    IS the problem on a specific, or all workstations (ie is it always a single user db, or is it specific workstations

  5. #5
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by ranger3484
    I created a MS Access data base that is working great but when mulitiple people try to access it at the same time, they're getting messages saying that the table is locked and the db will not open. The first person to use the table has no problems but everybody else has to wait until the first person closes the db before they can open it up and edit it. I've played around with all combinations of permissions but the problem persists. Any thoughts?
    In MS ACCESS menu goto Tools - options - Advanced Tab.
    Select the default record locking you want in your case, Choose NO Lock

    You also need to think that if two or more users are attempting to edit one same record on same field, the first one to get to it, should lock the table until exiting from that record, otherwise, there will be surprises of seeing different values. In which case you want to lock it during edit mode.
    Last edited by fredservillon; 11-25-05 at 21:37.

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question

    Quote Originally Posted by fredservillon
    In MS ACCESS menu goto Tools - options - Advanced Tab.
    Select the default record locking you want in your case, Choose NO Lock

    You also need to think that if two or more users are attempting to edit one same record on same field, the first one to get to it, should lock the table until exiting from that record, otherwise, there will be surprises of seeing different values. In which case you want to lock it during edit mode.
    Fred,

    Would splitting the database also be something that would help too? When I inherited a database where the entire database was on the server, we had issues like that even with the proper locking options set. Just inquiring....

    BUD

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If your access version supports record level locking (A2000 on) then I would never advocate No Locks (unless effectively (by design) there are no situations where you could have more than one user trying to update the same record at thre same time.

    There were very good (performance) reasons for using no locks when Access used page locking.

  8. #8
    Join Date
    Oct 2005
    Posts
    178

    Wink

    Quote Originally Posted by Bud
    Fred,

    Would splitting the database also be something that would help too? When I inherited a database where the entire database was on the server, we had issues like that even with the proper locking options set. Just inquiring....

    BUD
    If you split the database and you have a guaranted merging solution you can, but still, you'll the possibility of more than one users using the same table.

    Another you can do is use a disconnected recordset for each user dynamically, or better yet, design an unbound controls in a form by using variables for each field. This way you don't open the table until the user click a save button, that is, you don't open your table yet when users are edit your unbound controls or variable fields, then when the user click a command save button, you open your table or recordset and save and immeidately close it after the update command.
    Our institution bought a software with Access backend database and I have more that 20 users a time and I had this problem unitl I designed my own front-end application using varaible field technique as I mentioneed above.
    And now I don't see any locking by users anymore.
    Get your VB up and start.
    Last edited by fredservillon; 11-27-05 at 14:34.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    from the THIRD post in the thread:
    first and last names from the parent xls table on our server

    can XLS be a multi-user data source in Access????
    i may be wrong (never had the courage to try opening an XLS from Access for multi-user) but something i saw/read/tried/imagined tells me that your problem might be there.

    a brief glance at "help" this evening was, as usual, unhelpful.

    don't know if it will resolve your base problem ( = the "master" data being in XLS), but maybe you could try an import with transferspreadsheet? or a file copy of the XLS to the client and then import/link.

    izy
    Last edited by izyrider; 11-26-05 at 14:02. Reason: THIRD not FIRST
    currently using SS 2008R2

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Agree with fredservillon, that access can have real problems if you are using bound controls and multiple users. The problem is to do with the default data back end JET) not Access itself.

    However the problem intially reported is that the first user locks out all others. These symptoms to me that Access is being opend exclusively. Record locking is a possible symptom but open exclusive is in my view the first culprit to look at.

    can you identify how many concurrent users you have (not total number of users, but numbers trying to use the app at the same time)

    can you confirm if yoiu have split your app into 2 parts (the back end (data) in one half, the front end (the forsm, reprots etc... the other)

    if you have split the app are you using a common front end or are users using their own local copy of the application.

  11. #11
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by izyrider
    from the THIRD post in the thread:
    first and last names from the parent xls table on our server

    can XLS be a multi-user data source in Access????
    i may be wrong (never had the courage to try opening an XLS from Access for multi-user) but something i saw/read/tried/imagined tells me that your problem might be there.

    a brief glance at "help" this evening was, as usual, unhelpful.

    don't know if it will resolve your base problem ( = the "master" data being in XLS), but maybe you could try an import with transferspreadsheet? or a file copy of the XLS to the client and then import/link.

    izy
    Hi IzyRider
    I would not recommend opening relational Access database in excel. YOu will have corruption that could render your database unusable. If the user clicked the wrong command button and choose a wrong choice from option given it could force your database to accept unacceptable changes and your database could be corrupted.

  12. #12
    Join Date
    Nov 2005
    Posts
    13
    First of all, thanks to everyone who offered suggestions. Maybe I should have been a little more clear as to exactly what I am doing.

    The db that I set up is a Read & Initial Board. Traditionally a Read & Initial board is a hardcopy listing of all people in your workplace. Next to each person is a series of checkboxes (I have 10 checkboxes next to each name). A document may come out about workplace safety and thus be put on the R&I board. An employee will read the safety document, then check the box next to his name indicating that he has read the document. We currently employ about 150 employees, however many are temporary and we see an inflow/outflow of employees at a rate of about 10 per week. Thus the names of these employees is always changing. The XLS file on our server is used for a scheduling program and I am simply reaching into it, grabbing only what I need, first and last name. I am using this XLS file from the scheduling program since it's always an up-to-date record of active employees. No input whatsoever is actually pushed or can be pushed backup to the server's people.xls file.

    Since I don't want to, nor can, write to the people.xls file on the server I setup a seperate table in Access then merged the filtered names from the people.xls file into my seperate table. This table includes 10 additional yes/no fields for the checkboxes next to each person's name. This table does not reside on the server but simply within the Access database on the share drive. People have full access to write to this table in the yes/no fields.

    A form was created that displays the employee name in a dropdown box, then next to the dropdown box is a series of 10 checkboxes. When an employee reads the required document he simply checks the box related to the particular message indicating that he's read it. I do not want nor need any special control to ensure that one employee isn't simply checking all boxes or unchecking somebody elses boxes. If I open the db up, select my name I can check or uncheck my boxes with no problems. If somebody else opens up the db at the same time they get a warning that it cannot be opened, that a table is locked and to try again later. This occurs upon start up and before they even get to the form that would allow them to choose their name (or anybody's name for that matter). I have selected "No Locks" but the problem still remains. Nobody's fooling around in design view either. I've even tried opening the db on two seperate computers simotaneously and constantly get the warning about locked tables. Choosing "No Locks" isn't working.
    Last edited by ranger3484; 11-27-05 at 03:38.

  13. #13
    Join Date
    Oct 2005
    Posts
    178

    Wink

    Hi ranger3484
    Now that I know how your system is all about, let me suggest something that should work

    I undestand that when you say you just grabbed data from the 2 excel applicaitons(people's names and schedule) means that you use copy and paste right? Make sure you are not having hot link to them.

    Design a form that looks like what you have right now but use the unbound controls for you user interface objects, like the text boxes, combo drop down, and check boxes.

    Here's the process flow

    1. When the form is open, open the database tables and read the field values from that you want to display in the form and assign them to variables first. Use array variables if you are comfortable with it, then close the table/s and disconnect as soon as you assigned them to the variables.

    2. Assign these variables to the value properties of your form control objects

    By Doing this the users are actually working on values not link to your table thus your tables in the database are always free from being locked down.

    3. Once the user answers all the paramenters in your form when he/she click
    on save button, open the tables again in your database and assign the variable to the corrensponding fields in yur table and update the table and close immediately and disconnect.

    The idea here is not to have a hot link to the tables.

Posting Permissions

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