Hi Guys,  Im trying to run a set of queries (make tables, crosstabs
normal queries) in a loop.  Im getting a massive headache with this
because I get the following when I try and run the application.

Run Time Error 3211

" The database engine couldn't lock table "tablename" because it is
alredy in use by another person or process."

 Ive got a Loop in my code which runs a set of make table and Crosstab
Queries.  The first time the queries are run there is no problem. 
However, on the second pass I get the Run Time Error on a make-table
query.  (Bold  line below)

The Function is set out as follows:

--------------------------------------------------------------------------------------------
Public Function RunQueries()
  
  DoCmd.OpenQuery "make_table_1", , acReadOnly
  DoCmd.OpenQuery "make_table_2", , acReadOnly
    
  DoCmd.OpenQuery "make_table_3" , , acReadOnly
  DoCmd.OpenQuery "make_table_4", , acReadOnly  (<-----Error on this
line)

  DoCmd.OpenQuery "make_Crosstab_1", , acReadOnly
  DoCmd.Close acQuery, "make_Crosstab_1"
  
  DoCmd.OpenQuery "make_Crosstab_2", , acReadOnly
  DoCmd.Close acQuery, "make_Crosstab_2"
  
  DoCmd.OpenQuery "delete_query", , acReadOnly
  
  DoCmd.OpenQuery "query_1", , acReadOnly
  
  DoCmd.OpenQuery "make_Crosstab_3", , acReadOnly
  DoCmd.Close acQuery, "make_Crosstab_3"
  
  DoCmd.OpenQuery "make_Crosstab_4", , acReadOnly
  DoCmd.Close acQuery, "make_Crosstab_4"
   
  DoCmd.OpenQuery "query_2", , acReadOnly
  DoCmd.Close acQuery, "query_2"
  
End Function
--------------------------------------------------------------------------------------------------------

I Have tried using   DoCmd.Close acTable, "tablename"    before the
query-set is run, but this doesnt seem to help.

I would massively appreciate it if you could offer any words of wisdom
about this... Its all that is holding this system back and its driving
me mad.

Very Best Regards

Tim

This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.

Can you copy the SQL statements from the 4 make table queries so that I 
can see exactly what the syntax of the SQLs are.  Also, are any forms open 
at the time this is running? This might give me a clue as to the problem. 
There are several reasons for this error to occur but I need more 
information to give you an informed opinion.

My suspision is that it has to do with the make-table queries. When a make-
table query runs and the table name already exists, it deletes the table 
first before recreating it. This is where I suspect the problem to be. You 
can't delete a table in use so the make table will not run.

Not knowing exactly what you are trying to accomplish with the loop also 
makes it difficult to know how to best fix the problem. The SQLs of the 
queries may help with that.

Thanks,

Amy

> Hi Guys,  Im trying to run a set of queries (make tables, crosstabs
normal queries) in a loop.  Im getting a massive headache with this
because I get the following when I try and run the application.

Run Time Error 3211

" The database engine couldn't lock table "tablename" because it is
alredy in use by another person or process."

 Ive got a Loop in my code which runs a set of make table and Crosstab
Queries.  The first time the queries are run there is no problem. 
However, on the second pass I get the Run Time Error on a make-table
query.  (Bold  line below)

The Function is set out as follows:

---------------------------------------------------------------------------
-----------------
Public Function RunQueries()
  
  DoCmd.OpenQuery "make_table_1", , acReadOnly
  DoCmd.OpenQuery "make_table_2", , acReadOnly
    
  DoCmd.OpenQuery "make_table_3" , , acReadOnly
  DoCmd.OpenQuery "make_table_4", , acReadOnly  (<-----Error on this
line)

  DoCmd.OpenQuery "make_Crosstab_1", , acReadOnly
  DoCmd.Close acQuery, "make_Crosstab_1"
  
  DoCmd.OpenQuery "make_Crosstab_2", , acReadOnly
  DoCmd.Close acQuery, "make_Crosstab_2"
  
  DoCmd.OpenQuery "delete_query", , acReadOnly
  
  DoCmd.OpenQuery "query_1", , acReadOnly
  
  DoCmd.OpenQuery "make_Crosstab_3", , acReadOnly
  DoCmd.Close acQuery, "make_Crosstab_3"
  
  DoCmd.OpenQuery "make_Crosstab_4", , acReadOnly
  DoCmd.Close acQuery, "make_Crosstab_4"
   
  DoCmd.OpenQuery "query_2", , acReadOnly
  DoCmd.Close acQuery, "query_2"
  
End Function
---------------------------------------------------------------------------
-----------------------------

I Have tried using   DoCmd.Close acTable, "tablename"    before the
query-set is run, but this doesnt seem to help.

I would massively appreciate it if you could offer any words of wisdom
about this... Its all that is holding this system back and its driving
me mad.

Very Best Regards

Tim

This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.