error "database engine could not lock table because it is already in use by other"

[ewarts]

Hey Guys,

I have an access database with a few tables and forms. I was trying to add a new combo box to one of my forms and to do so i needed to add the referenced data to my reference data table. simple or so I thought, but while attempting to do so I got the following error ""database engine could not lock table because it is already in use by other " . I am currently the only user with access to this database so i'm at a lost as to how it is possible that the database is locked by another user.

Please can anyone assist.

[matthooper]

Did you have the table open?

[ewarts]

While trying to add the combo box to the form? No I don't think so.

[matthooper]

and are you using code to add to the list or are you using the horible access control thingie...

[ewarts]

No codes just the access standard procedures.

[missinglinq]

Please remember to provide a meaningful Title for any threads you start! This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions. It is difficult for the experts to answer questions when there is not enough details provided to understand the problem.

Please take the time to read the Posting Guidelines, paying particular attention to Give a clear title to your question.

http://www.thescripts.com/forum/thread559246.html

Thank you and Welcome to TheScripts!!

Linq ;0)>

[Scott Price]

Hey Guys,

I have an access database with a few tables and forms. I was trying to add a new combo box to one of my forms and to do so i needed to add the referenced data to my reference data table. simple or so I thought, but while attempting to do so I got the following error ""database engine could not lock table because it is already in use by other " . I am currently the only user with access to this database so i'm at a lost as to how it is possible that the database is locked by another user.

Please can anyone assist.

First of all, check to make sure that there is no .ldb file residing in the same folder as your database that remains there after you have closed the db, if there is, delete the .ldb file. (this is an access generated file that logs which users are accessing which parts of the database, to lock out/avoid conflicts with one user accessing the same record as another at the same time.)

I had a similar problem happen to me rather intermittently. I tracked it through a combination of deductive reasoning and intuition :-) to closing the db after having failed to compile any code changes in the VBA editor window.

From that I learned quickly to Compile, Save, Check Compile status, Save again before leaving the VBA editor window.

Another suggestion is to make sure you Compact-Repair and backup your database on a regular basis, at least once a day when you finish for the evening.

If you are interested in how to prevent corrupted databases, this is a very good page dedicated to just that: http://allenbrowne.com/ser-25.html

Just for reference purposes, here is Allen's Recovering corrupted databases page: http://allenbrowne.com/ser-47.html

Hope this helps you with your problem.

Regards,
Scott

[ewarts]

First of all, check to make sure that there is no .ldb file residing in the same folder as your database that remains there after you have closed the db, if there is, delete the .ldb file. (this is an access generated file that logs which users are accessing which parts of the database, to lock out/avoid conflicts with one user accessing the same record as another at the same time.)

I had a similar problem happen to me rather intermittently. I tracked it through a combination of deductive reasoning and intuition :-) to closing the db after having failed to compile any code changes in the VBA editor window.

From that I learned quickly to Compile, Save, Check Compile status, Save again before leaving the VBA editor window.

Another suggestion is to make sure you Compact-Repair and backup your database on a regular basis, at least once a day when you finish for the evening.

If you are interested in how to prevent corrupted databases, this is a very good page dedicated to just that: http://allenbrowne.com/ser-25.html

Just for reference purposes, here is Allen's Recovering corrupted databases page: http://allenbrowne.com/ser-47.html

Hope this helps you with your problem.

Regards,
Scott

Scott,

I think what's happening here is, when the firm runs a daily back while the file is being updated then that backup file will still be open even though I may close the original. I'm hoping this is the case, can you or anyone confirm this?

[Scott Price]

Scott,

I think what's happening here is, when the firm runs a daily back while the file is being updated then that backup file will still be open even though I may close the original. I'm hoping this is the case, can you or anyone confirm this?

Can't confirm it without seeing the whole setup physically before my very own eyes, no...

Why would they be running the daily backup during a time that someone else is working on the db? Is this a 24 hour operation, where they don't have a downtime? If it IS 24 hour, is it mission-critical so the db can't be 'shut down for maintenance' for the 15 minutes or so that it would take to do the Compact/Repair & Backup?

You might want to look at a better daily maintenance program schedule if at all possible, to avoid this very kind of conflict... (If this is in fact the problem that you are having!)

Regards,
Scott

[ewarts]

Can't confirm it without seeing the whole setup physically before my very own eyes, no...

Why would they be running the daily backup during a time that someone else is working on the db? Is this a 24 hour operation, where they don't have a downtime? If it IS 24 hour, is it mission-critical so the db can't be 'shut down for maintenance' for the 15 minutes or so that it would take to do the Compact/Repair & Backup?

You might want to look at a better daily maintenance program schedule if at all possible, to avoid this very kind of conflict... (If this is in fact the problem that you are having!)

Regards,
Scott

They backup on a nightly basis however I may have left the db running over night.

[Scott Price]

They backup on a nightly basis however I may have left the db running over night.

Well, theoretically it SHOULDN'T be that, however, in the world of computers anything MAY happen...

If you still get the error after compiling, saving, compacting and repairing, then it's time to follow Allen's suggestions on recovering from corruption.

Regards,
Scott

[ewarts]

I will try that suggestion, BTW I got a snapshot of the past 30 days backup and from first glance noticed that several days were opened, this might be the cause/root of my problem. I will give catalog my findings and report.

Thx all

[JimB]

I had the same issue because of a linked list box that I wanted to refresh via a macro but the list box was on a form that was actively displayed when the refresh macro kicks off. I fixed it by altering the macro to close the form momentarily while the list box was updated and then quickly re-open the form. No conflict thereafter.