Need help to unravel Database Table related error message

[u0107]

Hello,

In my application, I need to create temporary files, use them as sources for queries which in turn populate forms. When a button on the form is clicked, I need to delete these temporary files, re-generate the queries and refresh the forms.

I am encountering the following error which I am unable to unravel:

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

Could someone guide me where to look for as to who or what process is
locking this up? I am somewhat familiar with the use of the debugger.

For some more info on how the table is created:

I use the following syntax for initial deletion of any old file with the same name

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cat.Tables.Refresh

cat.Tables.Delete "tbltempTDW"

the error message is displayed when I try to execute the
cat.Tables.Delete "tbltemTDW" statement.

Thanks in advance.

Uttam

 

[SydneyGeek]

Re: Need help to unravel Database Table related error messag

Hi Uttam,
That message usually appears when you try to delete a table that is already locked. When you run the routine, are there any forms / queries open that directly or indirectly reference that table? If so, make sure the form is closed before running the code. If that means closing the form from which you trigger the code, I'd suggest putting that particular button onto a new, unbound form.
HTH
Denis

 

[u0107]

Re: Need help to unravel Database Table related error messag

Hello,

Thank you for trying to help.

I do use the Table as the source for a query which in turn is the source for a form.

As soon as a button is clicked, I extract the relevant values from the form (based on which button is clicked) and close the form.

Still this problem occurs.

When I moved the delete command statement to a line just before the point where I recreate the table with the same name, the error still occurs.

Just to explain a little:

Form_31240 is the name of my form on which there are a number of select buttons. Associated with each of the select buttons are 5 fields whose values determine what data I load the temp table with.

Private Sub New_Form_31240_Select_Button_Click subroutine is the event procedure associated with the On Click event for the select button.

Here is what the code now looks like

Private Sub New_Form_31240_Select_Button_Click()
On Error GoTo Err_New_Form_31240_Select_Button_Click

MsgBox ("Select Button pressed")
APP_Code = Forms!New_Form_31240!TPH_APP_Code
TCH_Code = Forms!New_Form_31240!New_Form_31240_Select_Subform!TDW_TPH_TCH_Technology
TTY_Code = Forms!New_Form_31240!New_Form_31240_Select_Subform!TDW_TPH_TTY_Testing_Type
MOD_No = Forms!New_Form_31240!New_Form_31240_Select_Subform!TDW_TPH_Module_Srl_No
TP_Ver = Forms!New_Form_31240!New_Form_31240_Select_Subform!TDW_TPH_Test_Plan_Ver

'----------------------------------------------
DoCmd.Close
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cat.Tables.Refresh
<snip><snip><snip><snip><snip>
cat.Tables.Delete "tbltempTDW"
cat.Tables.Append tdf

I get an error message when

cat.Tables.Delete "tbltempTDW" is executed.

If in debug mode, I skip this cat.Table.Delete line and go directly to cat.Tables.Append I get an error saying tbltempTDW already exists.

If you can, could you please guide me as to where I should look within the Debugger windows where I could detect which process /user is using this table? At the present there is no one else who is using this table as the app is being developed on a standalone machine.

Thanks once again.

Cheers!

Uttam

=============

 

[SydneyGeek]

Re: Need help to unravel Database Table related error messag

Hi Uttar,
thanks for showing the code. Before I dig in too far, what is the RecordSource for Form_31240?
Regards
Denis

 

[u0107]

Re: Need help to unravel Database Table related error messag

Hello,

Thank you once again.

There are essentially two forms New_Form_31240 and its corresponding subform New_Form_31240_Subform.

The record source for both the forms are queries - two different queries, one for each Form / subform.

The New_Form_31240 is called from another "Menu type of a Form". The On Click event of the select button on this "Menu Type of Form" and which opens New_Form_31240 has the code to generate these two queries on the fly. First the query for the Parent (New_Form_31240) is created and then the query of the child is created. These Queries are created using the standard ADO kind of code which is available in Allison Balter's book.

After this query is generated (and I get no problems in generating these queries), the form is opened which in effect shifts control to the Event Procedure associated with the "On Open" event of New_Form_31240.

The problem occurs in the "On Click Event" for the Select Button on the subform for New_Form_31240.

Apologies for a long answer but I have tried to anticipate all your questions and tried to include all the info that I have thought you might need.

Once again - Thank you.

Cheers!

Uttam