+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: The database could not lock table 'tablename' because it is already in use by another

  1. #1
    Barn Regular Colper is an unknown quantity at this point Colper's Avatar
    Join Date
    Dec 2009
    Posts
    58
    Rep Power
    1

    The database could not lock table 'tablename' because it is already in use by another

    Can someone please help me with this issue? I have a database which I created and it’s being shared by other users. When there is more than one (1) user using the database I get this error:


    “The database could not lock table (tablename) because it is already in use by another person or process.”

    Thank you in advance.

  2. #2
    Moderator don94403 is a jewel in the rough don94403 is a jewel in the rough don94403 is a jewel in the rough don94403's Avatar
    Join Date
    Mar 2008
    Location
    San Mateo, CA, USA
    Posts
    172
    Blog Entries
    8
    Rep Power
    5

    What version of Access are you using? There were substantial changes in the multi-user features, between 2003 and 2007.
    question = 2B || !2B

  3. #3
    Barn Regular Colper is an unknown quantity at this point Colper's Avatar
    Join Date
    Dec 2009
    Posts
    58
    Rep Power
    1

    Access 2003.

  4. #4
    Moderator don94403 is a jewel in the rough don94403 is a jewel in the rough don94403 is a jewel in the rough don94403's Avatar
    Join Date
    Mar 2008
    Location
    San Mateo, CA, USA
    Posts
    172
    Blog Entries
    8
    Rep Power
    5

    And when do you get this error, immediately when someone else accesses the database, or only when the user tries to perform some operation? And if the latter, what operation triggers the error? Be as specific as you can be, such as showing code or SQL, if appropriate.
    question = 2B || !2B

  5. #5
    Barn Regular Colper is an unknown quantity at this point Colper's Avatar
    Join Date
    Dec 2009
    Posts
    58
    Rep Power
    1

    It happens when more than one user tries to update/inputa data or tries to open any form in the database. I don't know what code to show, there is a lot of coding here so I have no idea where to find the corresponding code to show you.

    Thank you.

  6. #6
    Moderator don94403 is a jewel in the rough don94403 is a jewel in the rough don94403 is a jewel in the rough don94403's Avatar
    Join Date
    Mar 2008
    Location
    San Mateo, CA, USA
    Posts
    172
    Blog Entries
    8
    Rep Power
    5

    OK, I would need to know the following:

    1. How is this database being shared? Is it a single .mdb file on a network drive, or is the database "split" into "front-end" and "back-end" .mdb's? If it is being used by multiple users, it definitely should be split. If it IS split, do each of the users have their own copy of the "front-end" .mdb? They should have.

    2. Is the database "secured" with user-level security? This means that users must LOGIN with a password in order to access the database.

    3. Do I understand that the database is fully accessible as long as only one user is trying to use it at the same time?
    question = 2B || !2B

  7. #7
    Barn Regular Colper is an unknown quantity at this point Colper's Avatar
    Join Date
    Dec 2009
    Posts
    58
    Rep Power
    1

    1. The database is ina single .mdb file on a share drive. Users donot need a password, only username. I have created two other databases and have never heard or done anything with front or back end or split. They are on the same share drive different folders and they are working perfectly.

    2. Yes, it's secure with user-level security. Username only no password.

    3.Yes, only one user can edit or input data at one time.

    Thanks....

  8. #8
    Moderator don94403 is a jewel in the rough don94403 is a jewel in the rough don94403 is a jewel in the rough don94403's Avatar
    Join Date
    Mar 2008
    Location
    San Mateo, CA, USA
    Posts
    172
    Blog Entries
    8
    Rep Power
    5

    Quote Originally Posted by Colper View Post
    1. The database is ina single .mdb file on a share drive. Users donot need a password, only username. I have created two other databases and have never heard or done anything with front or back end or split. They are on the same share drive different folders and they are working perfectly.

    2. Yes, it's secure with user-level security. Username only no password.

    3.Yes, only one user can edit or input data at one time.

    Thanks....
    OK, I would say that you have just been lucky up 'til now. Access databases should always be split when you need to have simultaneous use by multiple users on a network. Here's some information on this:

    Using MS Access: Multiple users, joerg mayer, networked system
    Multiuser Databases in Microsoft Access
    Time To Link Your Microsoft Access Program To MS Access Data File | Daily Access Tips
    http://www.ozgrid.com/forum/showthread.php?t=34665
    question = 2B || !2B

  9. #9
    Barn Regular Colper is an unknown quantity at this point Colper's Avatar
    Join Date
    Dec 2009
    Posts
    58
    Rep Power
    1

    I really appreciate your help. I read all of those links and that's great information. I will try to implement it as needed as possible.

    Now, my thing is that the other two databases I created are working just fine without any problems and I never did the split. So I am curious as to why it has worked. Another thing to keep in mind is that this database I am talking about I did not created from scratch.

    This database was already created by another user and all I did was redesigned it.

    Thank you once again!

  10. #10
    Moderator don94403 is a jewel in the rough don94403 is a jewel in the rough don94403 is a jewel in the rough don94403's Avatar
    Join Date
    Mar 2008
    Location
    San Mateo, CA, USA
    Posts
    172
    Blog Entries
    8
    Rep Power
    5

    As I said, you were lucky. The specific reasons for lock-ups are hard to determine, and are really unimportant, since the only way to avoid them is to split the database. To satisfy your curiosity, though, it involves how many tables are in the database, and what kind of relationships exist, how many records are in the tables, how many users are trying to access the same tables at exactly the same time, etc. etc. etc. etc.

    The only safe rule to follow is: in every case where multiple users need to be able to access the data simultaneously on a network, SPLIT THE DATABASE. It's really pretty easy to do. There's even a Wizard for it, although I don't bother even using it. The whole point is to import all data (TABLES) from the "front-end" .mdb into a new "back-end" .mdb. That's the .mdb file that will be "linked" to by all the users' "front-end" copies. When you have done that, open the front-end .mdb and [gasp!] DELETE ALL THE TABLES. Then, using the Linked Table Manager, link all the tables back in (don't import them, link to them), they will appear in the Tables Tab of the Database Window with little arrows beside them. The application will then work as it did before. Now, make a copy of this "front-end" for each user and install it on their work station. You may need to repeat the Linked Table Manager function on each one, depending on how each one has mapped the shared drive on the network, but it's a 30-second operation.

    One word of caution: whenever you do something like this, always make a backup copy of the file before you begin -- JUST IN CASE something goes wrong!
    question = 2B || !2B

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Similar Threads

  1. "File sharing lock count exceeded…"
    By Colper in forum Microsoft Access
    Replies: 2
    Last Post: December 7th, 2009, 10:44 PM
  2. Delete selected information from database from table
    By Arshan in forum ASP Development
    Replies: 3
    Last Post: September 2nd, 2009, 10:27 AM
  3. Database table design
    By Ziggy in forum Database Design Help
    Replies: 17
    Last Post: February 16th, 2009, 01:55 PM
  4. Lock page/recordset?
    By Rebelle in forum ASP Development
    Replies: 37
    Last Post: October 30th, 2008, 03:34 PM

Bookmarks

Posting Permissions

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

SEO by vBSEO