Results 1 to 5 of 5
  1. #1
    cwillson is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    17

    Macro containing multiple RunSQL commands crashes with "The database engine could not lock table"

    Hi peeps,



    I have created a Macro in Access 2010 which runs a number of SQL commands in succession. Sometimes it seems to run ok, but most of the time I get an error message stating that "The database engine could not lock the table XXX because it's already open by another person or process".

    Some of these RunSQL commands involve updating 53k rows. Could it be that they are still running on the table while the macro is trying to call the next RunSQL command, or would it be something else?

    If it's the former, is there any way to add an element to the macro which ensures that the previous SQL command can complete before the next one is started?

    Thanks,

    Chris

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the volume is not the issue.
    IS there someone else in the db?
    Is the db split? (it should be for multi users, this prevents multiuser lock)

    SOMETIMES, in a single user db, if you are the only user and the db gets locked,
    close the db,
    see if the lock file still exists, .ldb, or .laccdb.

    if there are no other users,
    and you are not in the db,
    and the lock file still exists, then delete the lock file.
    This usu clears the problem.

  3. #3
    cwillson is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    17
    Thanks Ranman256

    IS there someone else in the db?
    -Nope, just me

    Is the db split? (this prevents multiuser lock)
    -Not sure what this means? Could you explain please?

    See if the lock file still exists, .ldb, or .laccdb.
    -I'm assuming this is a hidden file within the same folder where the db is saved? I've made sure I can view these, but with the db open or closed I cannot see a lock file. Is this somewhere else?

    C

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    The lock file name is "YourDatabaseName.Iaccdb". It's in the same folder as the database. The file should be automatically created when the DB opens, and automatically deleted when the DB closes. I don't think it's hidden. Sometimes if a DB closes abnormally, it will leave the lock file as an orphan, and you can manually delete it.
    Have you tried a Compact and Repair?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "The database engine could not lock the table XXX because it's already open by another person or process".
    Does any macro step perform an open action on any form that is bound to a table you're trying to update or append to? Does it open an updatable query, and in another step you're trying to update/append to the table the query has a lock on? Point I'm trying to make is that since you've declared no one else has the db open, the macro process (or a prior process) has opened a form or query and that is causing the sql to fail. If so, a step may be required to close the object prior to the sql execution. Kinda guessing since I very seldom use macros.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 05-03-2016, 08:44 AM
  2. Replies: 5
    Last Post: 10-27-2014, 07:59 PM
  3. Replies: 2
    Last Post: 07-14-2014, 10:34 AM
  4. Replies: 1
    Last Post: 06-27-2014, 10:51 AM
  5. Database engine could not lock table issue
    By ganeshvenkatram in forum Access
    Replies: 2
    Last Post: 06-28-2011, 11:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums