Table Lock - Run-time Error 3211

[perlfan]

Hi - this has already taken me days....:-/ When I try to add a column via SQL to the table employees I get the following error:

The database engine could not lock table employees because it is in use by another person or process.

With my other tables the command works (tried it to make sure the command works). I can also add a column to the table employees if it's a MySQL database. With Access as backend I get the above error. The difference of this table compared to the other tables is that I select data from it once at the beginning (part of the login process). BUT - I already tried rs.close and I also dropped all tables after the login and relinked them. I still get the same error! It seems as if the table itself is different from the other tables, but this is not the case in my opinion. I attached the backend database so you can have a look. BTW: I need all this to implement an automatic update of my app, e.g. to add new features etc., so I distribute a new version that automatically does the required changes in the background.

I'd be very grateful for advice as this is really freaking me out.

This is the SQL command:

SQL  = "ALTER TABLE employees ADD column preselect_translator_CAT_tool int,  preselect_translator_availability int,  preselect_translator_language_pair int"
                                        db.Execute SQL

Thank you in advance - FRANK

 

[arnelgp]

i since this is a backend db, i supposed someone in the network is using it, through fe.

 

[JHB]

Can't recognize any problem.
It adds the fields as requested when you execute the code in a form!
And also as arnelgp writes!

 

[arnelgp]

the technique for changing table on the back end is to create a table in the backend, ie tblMaintenance with one field, ForMaintenance (Boolean). link this table to all fe.

create a form with timer event to check the value of ForMaintenance field on an interval.
open this form (on all fe) on startup through autoexec macro and set its to be hidden.

now, on the load event of this form check the value of this field, if it is true, show a message that the db is in maintenance mode, quit the application.
if it is false, then proceed as normal.

the hidden form, must check this field in time interval, if it is true then warn the user through message then on the exit of msgbox, close all open object, quit the application.

you will have to set this field to True if you want to make changes to the BE.
after setting it to True, wait for a while, since some users might not be closed their db for sometime, then make changes to the backend.

after making all changes, set this value to false. your users will be able to use your db again.

 

[perlfan]

Thank you for your ideas - I can 100% exclude that other users are accessing the database - it's a local test database which is only openend by me. AND - I can alter other tables. Thank you for other ideas.

FRANK

 

[JHB]

Could you post the Front-end database also, as mention the code execute okay in the Back-end database.