Unable to delete Temp Table after the Form and Recordset are closed

[time2hike]

I have a series of forms that allow the users to update data in a temporary table and then delete the records in the permanent table and append the records from the temporary table to the permanent table. The final step is to delete the temporary table. I have everything working up to the deletion of the temporary table. I am receiving a run-time error '3211': The database engine could not lock table 'Temporary_1' because it is already in use by another person or process.

The temporary table is the source for the form that the user updates and this command runs from. So, the last thing we do before attempting to delete the temporary table is close the form. I thought maybe the issue is that we use the temporary table in our recordset so, we would need to close the record set before deleting the table, but when I try to do this I get a Run-time error '3704' Operation is not allowed when the object is closed. This confuses me because I open the recordset and do not close it, why would it not need to be closed?

I do not see anywhere else in my code or my forms that the temporary table is being used and should be locked. Any advice is greatly appreciated.

Private Sub cmdTest_Click()

    Dim conn As ADODB.Connection
    Dim stPath As String
    Dim rst As ADODB.Recordset
    Dim sSQL As String
    Dim rst1 As ADODB.Recordset
    Dim sSQL1 As String
    Dim dblRptOwnr As Double
    Dim stRptTitle As String
    Dim stTblName As String
    
    dblRptOwnr = [Forms]![frmDialogMngrSbprjtRptGrpSelect]![cmbRptOwnr]
    stRptTitle = [Forms]![frmDialogMngrSbprjtRptGrpSelect]![cmbRptTitle]
    stTblName = "TBLMNGRSBPRJTRPTDETAILS_" & GetSBPRJTRPTGRPID(dblRptOwnr, stRptTitle)
    ' Set the string to the path of your database
    stPath = CurrentDb.Name
    Debug.Print stPath
    ' Open connection to the database
    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & stPath & ";"
    conn.Open
    'Select the Current MNGRSBPRJTRPTGRPID Records in TBLMNGRSBORJTRPTDETAILS
    sSQL = "SELECT * " _
        & " FROM TBLMNGRSBPRJTRPTDETAILS " _
        & " WHERE (((TBLMNGRSBPRJTRPTDETAILS.SBPRJTRPTGRPID)=" & [Forms]![frmDialogMngrSbprjtRptGrp]![txtRptGrpID] & "));"
    Set rst = New ADODB.Recordset
    rst.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    'Delete all of the currently selected MNGRSBPRJTRPTGRPID Records in TBLMNGRSBORJTRPTDETAILS
    With rst
        Do While Not .EOF
            Debug.Print rst!SBPRJTRPTGRPID & ", " & rst!SubprojectID
            .Delete
            .MoveNext
        Loop
    End With
    'Append all of the records from the Temp TBLMNGRSBPRJTRPTDETAIL_MNGRSBPRJTRPTGRPID table
    sSQL1 = "INSERT INTO TBLMNGRSBPRJTRPTDETAILS ( SBPRJTRPTGRPID, SUBPROJECTID, ADD_BY, ADD_DTTM, MOD_BY, MOD_DTTM )" _
        & " SELECT SBPRJTRPTGRPID, SUBPROJECTID, ADD_BY, ADD_DTTM, MOD_BY, MOD_DTTM" _
        & " FROM " & stTblName _
        & " WHERE " & stTblName & "!FLAG = No"
    Debug.Print sSQL1
    Set rst1 = New ADODB.Recordset
    Debug.Print rst1.State
    rst1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    Debug.Print rst1.State

    DoCmd.Close 'Close the form based on the temporary table
    rst.Close
    Set rst = Nothing
    Debug.Print rst1.State
    rst1.Close 'Receiving Run-time error '3704' Operation is not allowed when the object is closed.
    'This confuses me because I open the recordset and do not close it, why would it not need to be closed?
    Set rst1 = Nothing
    DoCmd.DeleteObject acTable, stTblName 'Receiving Run-time error '3211':
    'The database engine could not lock table 'Temporary_1' because it is already in use by another person or process.

    

End Sub

[zmbd]

This appears to be a scope issue:

Close your record sets before you close the form.

The variables/pointers are only available within scope while within the calling procedure.

When you close the form, you kill the pointers within that scope. Normally Access is fairly forgiving about this; however, when it gets cranky, watch out.... it eats your data.... (wakawakawakawa kawakawakawakaw aka--- burp).

[time2hike]

Zmbd, I moved the closing of the recordsets to occur before the closing of the form. I am still getting the same errors:

Rst1 will not close I get a Run-time error '3704' Operation is not allowed when the object is closed.

Table will not delete I get a Run-time error '3211': The database engine could not lock table 'Temporary_1' because it is already in use by another person or process.

I added Debug.Print rst1.State(line s 45,47,and 52) and the result is always 0 even after line 46 where we open rst1. I know that the SQL statement (line 39-42) is executing because the records are being appended into the table. What I don’t understand is the state of 0 for the recordset. Is that because the SQL is an Insert?

If the recordset is not open, and we are not referencing this table anywhere else in our database shouldn’t closing the form that is based on the table free up the table to be closed? Would it matter that the form is a subform and we are closing the main form which closes the sub form?
Is there something else I should look for?

[zmbd]

Sorry,
I didn't catch that sSQL was an insert

because it's an action query you will never get an open state. I am guessing that because you used adLockOptimisti c the table was locked; however, because this is an action query, the pointer was lost before the lock is released.

let's try:
Original code block:
Line#44 delete
Line#46 Change:

rst1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

To

Conn.execute sSQL1

(I think that's correct...)

remove all other references to "rst1"

Still ensure that you have closed and set to nothing all of your record sets that have opened programatically proir to closing the form(s).

[ADezii]

It is not a good idea to use the Source argument of the Open method to perform an Action Query that doesnt return Records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be CLOSED. Call the Execute method of a Command object or the Execute method of a Connection object instead to perform a query that, such as a SQL INSERT statement, that doesnt return records.

[zmbd]

@ADezii:
Thank you for confirming that... I was only guessing as I would never had thought to open a recordset on an action query; however, I never had an understanding as to why not!

[time2hike]

Thank you both for helping me understand what I am doing wrong with this code. I have made the changes suggested; however, I am still getting Run-time error 3211': The database engine could not lock table 'Temporary_1' because it is already in use by another person or process.

I am able to manually delete the table once the form is closed, but I cannot delete the table from my VBA. I have included my revised code below. Can you see anything I should change to get this to work?

Private Sub cmdClose_Click()

    Dim conn As adodb.Connection
    Dim stPath As String
    Dim rst As adodb.Recordset
    Dim sSQL As String
    Dim sSQL1 As String
    Dim dblRptOwnr As Double
    Dim stRptTitle As String
    Dim stTblName As String
    
    dblRptOwnr = [Forms]![frmDialogMngrSbprjtRptGrpSelect]![cmbRptOwnr]
    stRptTitle = [Forms]![frmDialogMngrSbprjtRptGrpSelect]![cmbRptTitle]
    stTblName = "TBLMNGRSBPRJTRPTDETAILS_" & GetSBPRJTRPTGRPID(dblRptOwnr, stRptTitle)
    ' Set the string to the path of your database
    stPath = CurrentDb.Name
    ' Open connection to the database
    Set conn = New adodb.Connection
    conn.Provider = "Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & stPath & ";"
    conn.Open
    'Select the Current MNGRSBPRJTRPTGRPID Records in TBLMNGRSBORJTRPTDETAILS
    sSQL = "SELECT * " _
        & " FROM TBLMNGRSBPRJTRPTDETAILS " _
        & " WHERE (((TBLMNGRSBPRJTRPTDETAILS.SBPRJTRPTGRPID)=" & [Forms]![frmDialogMngrSbprjtRptGrp]![txtRptGrpID] & "));"
    Debug.Print "rst.sql: " & sSQL
    Set rst = New adodb.Recordset
    rst.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    'Delete all of the currently selected MNGRSBPRJTRPTGRPID Records in TBLMNGRSBORJTRPTDETAILS
    With rst
        Do While Not .EOF
            Debug.Print rst!SBPRJTRPTGRPID & ", " & rst!SubprojectID
            .Delete
            .MoveNext
        Loop
    End With
    'Append all of the records from the Temp TBLMNGRSBPRJTRPTDETAIL_MNGRSBPRJTRPTGRPID table
    sSQL1 = "INSERT INTO TBLMNGRSBPRJTRPTDETAILS ( SBPRJTRPTGRPID, SUBPROJECTID, ADD_BY, ADD_DTTM, MOD_BY, MOD_DTTM )" _
        & " SELECT SBPRJTRPTGRPID, SUBPROJECTID, ADD_BY, ADD_DTTM, MOD_BY, MOD_DTTM" _
        & " FROM " & stTblName _
        & " WHERE " & stTblName & "!FLAG = Yes"
    Debug.Print "sSQL1: " & sSQL1

    conn.Execute sSQL1

    rst.Close
    Set rst = Nothing
    
    DoCmd.Close 'Close the form based on the temporary table
    DoCmd.Close acTable, stTblName, acSavePrompt
    DoCmd.DeleteObject acTable, stTblName 'Receiving Run-time error '3211':
    'The database engine could not lock table 'Temporary_1' because it is already in use by another person or process.

End Sub

[zmbd]

Do you have Option Explicit set at the top of this code module?
If not, please place it as the very first line in this code module and then due a debug/compile from the VBE menu.
Let us know what happens.

[zmbd]

Also we might try:
Post#7 Line 49:DoCmd.Close
change to:
Post#7 Line 49:DoCmd.Close Objecttype:=acF orm, ObjectName:="form to close", Save:=acSaveNo
You will need to change the "form to close:" to the form's name (^_^)
This way we are sure that we are closing the intended form. Sometimes Access can be too smart for its own good (or ours!).

[time2hike]

zmbd, I revised the code to include the name of the form. Yes I am using Option Compare Database and Option Explicit. I am still receiving the Run-time error 3211': The database engine could not lock table 'Temporary_1' because it is already in use by another person or process.

This form (2) is accessed via a dialog form (1) with 2 unbound combo boxes. When I move the delete table to the Form Close of the dialog form (1) the table deletes. The concern is if the user changes the values of the unbound combo boxes on the dialog form (1) prior to closing it the temporary table will not be deleted. So I tried to force the close of the dialog form (1) from this form (2) and I get the same error message. Run-time error 3211': The database engine could not lock table 'Temporary_1' because it is already in use by another person or process.

Is there a way to delete all tables where the table name begins with 'Temporary_'. If so I could add that code to dialog form (1) and run it on the form close event and solve this issue.

I value your opinion. What do you think? Is this a better option? How would I go about this?

[ADezii]

Just out of curiosity, try disassociating the Temporary Table from the Form's Record Source prior to the Closing and Table deletion:

Me.RecordSource = ""

With DoCmd
  .Close    'Close the form based on the Temporary Table
  .Close acTable, stTblName, acSavePrompt
  .DeleteObject acTable, stTblName
End With

[zmbd]

it sounds as if form1 actually has the lock on the temp-table and not form2.

I don't use the ADODB method very often; thus, I'm not sure about what you have for table collection. I know in DAO there is the tabledef collection that one could for..each thru and check the names

[zmbd]

duh... sound of me hitting forhead on the desk many times... closed the record set and we didn't close the connection... forst-trees-leaf-happy.place

Post#7 Line 48:

conn.close
if not conn is nothing then set conn = nothing

Although I still think your issue is in form1 with the connection still active there.

[time2hike]

zmbd, it does not make sense that form1 connection is active since everything on form1 is a command or is unbound. However, I have resolved the issue by searching for tables that the system can identify as being the temp tables and deleting them. See code below. If you see any red flags in the code could you let me know? Thank you for all your help!!!

    Dim tbl As AccessObject, dB As Object
    Dim strMsg As String
    
    Set dB = Application.CurrentData
    For Each tbl In dB.AllTables
        If Left(tbl.Name, 24) = "TBLMNGRSBPRJTRPTDETAILS_" Then
            Debug.Print tbl.Name
            DoCmd.DeleteObject acTable, tbl.Name
        End If
    Next tbl

[zmbd]

I don't see anything obviously wrong with that last bit, keep in mind that the compare is case-sensitive.

Did you try closing the connection as I offered in the last post? Would be nice to know one-way or the other if that was the issue.