Table still visible after DROP TABLE statement - why?

[SIMMS7400]

HI Folks -

I have the following procedure running in my Access Database and it's working fine, but the one caveat is that even though I am deleting the table at the end of the procedure, I can still see it. Even if I close the DB and re-open, it's still there. Do you know why this is? Could it be my code?

Here's my main procedure:

Public Function SFDC_Partner_Partnership_Source()
On Error GoTo Proc_Err
    
    '::-- Initialize --::'
    If strActivate_Flag = 0 Then Call Activate_Modules
    
    Dim db As DAO.Database
    Dim ws As DAO.Workspace

    Dim strSQL As String, strTableList As String, strTempTable As String, strTable As String
    Dim strQuery As String, strExportTo As String, strDelim As String, strID As String
    
    Dim strFunctName As String, strStartTime As Date, strEndTime As Date, strTimeDiff As String
    Dim strStep As String, strSubject As String, strBody As String, strTo As String, strProcError As String
    
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.Databases(0)

    strID = Format(Hour(Now), "00") & Format(Minute(Now), "00") & Format(Second(Now), "00")
    strFunctName = "SFDC_Partner_Partnership_Source": strStartTime = Format(Now, "mm/dd/yyyy hh:mm:ss")
    strTempTable = "SFDC_PTRPTP"
    strQuery = "SFDC_to_MDM_PTRPTPSync"

    'Clear Tables
    Call DeleteTable(strTempTable)

    'Start a transaction to ensure all updates are run or rolled back
    ws.BeginTrans: strTFlag = 1
        
    strStep = "Create Temporary SFDC PTR / PTP Table"
    strSQL = "" & _
            "CREATE TABLE [SFDC_PTRPTP] (" & _
                "[PARTNERSHIPID] CHAR(9),[PARTNERSHIPALIAS] CHAR,[PARTNERSHIPACTIVEFLAG] SMALLINT,[PARTNERSHIPISDELETED] SMALLINT," & _
                "[PARTNERSHIP_EXECUTION_DATE] DATETIME,[MDM_BUSINESS_OWNER] CHAR" & _
                ",[PARTNERID] CHAR(9),[PARTNERALIAS] CHAR,[PARTNERACTIVEFLAG] SMALLINT,[PARTNERISDELETED] SMALLINT" & _
            ");"
    db.Execute strSQL, dbFailOnError: db.Close
    strStep = ""
    
    strStep = "Build PTR & PTP Table Content"
    strSQL = "" & _
            "INSERT INTO [SFDC_PTRPTP] (" & _
                " [PARTNERSHIPID] , [PARTNERSHIPALIAS], [PARTNERSHIPACTIVEFLAG], [PARTNERSHIPISDELETED], [PARTNERID]," & _
                "[PARTNERALIAS], [PARTNERACTIVEFLAG], [PARTNERISDELETED],[MDM_BUSINESS_OWNER],[PARTNERSHIP_EXECUTION_DATE]" & _
            ")" & _
            " SELECT DISTINCT" & _
                " Trim([SFDC_PARTNERSHIP].[MDM_PARTNERSHIP_ID]), Replace([SFDC_PARTNERSHIP].[PARTNERSHIP_NAME],""’"",""'""), [SFDC_PARTNERSHIP].[ACTIVE_FLAG]," & _
                "[SFDC_PARTNERSHIP].[IS_DELETED], Trim([SFDC_PARTNER].[MDM_PARTNER_ID])" & _
                " ,Replace([SFDC_PARTNER].[SALESFORCE_PARTNER_NAME],""’"",""'""),[SFDC_PARTNER].[ACTIVE_FLAG],[SFDC_PARTNER].[IS_DELETED]," & _
                "Trim([SFDC_PARTNERSHIP].[MDM_BUSINESS_OWNER]),[SFDC_PARTNERSHIP].[PARTNERSHIP_EXECUTION_DATE]" & _
            " FROM [SFDC_PARTNERSHIP]" & _
            " INNER JOIN [SFDC_PARTNER] ON [SFDC_PARTNERSHIP].[SALESFORCE_PARTNER_ID] = [SFDC_PARTNER].[SALESFORCE_PARTNER_ID]" & _
            " WHERE ([SFDC_PARTNER].[SALESFORCE_PARTNER_ID] = [SFDC_PARTNERSHIP].[SALESFORCE_PARTNER_ID] AND [SFDC_PARTNERSHIP].[MDM_PARTNERSHIP_ID] LIKE 'PTP*'" & _
                " AND [SFDC_PARTNER].[MDM_PARTNER_ID] LIKE 'PTR*' AND LEN([SFDC_PARTNERSHIP].[MDM_PARTNERSHIP_ID]) = '9' AND LEN([SFDC_PARTNER].[MDM_PARTNER_ID]) = '9'" & _
                " AND [SFDC_PARTNERSHIP].[PARTNERSHIP_NAME] IS NOT NULL AND [SFDC_PARTNER].[SALESFORCE_PARTNER_NAME] IS NOT NULL" & _
                " AND [SFDC_PARTNER].[ACTIVE_FLAG] = 1 AND [SFDC_PARTNERSHIP].[ACTIVE_FLAG] = 1)" & _
            ";"
    db.Execute strSQL, dbFailOnError: db.Close
    strStep = ""

    'commit all changes
    ws.CommitTrans: strTFlag = 0

    Set rs = db.OpenRecordset(strQuery)
    If rs.RecordCount > 0 Then
    
        strExportTo = str_Auto_ActionScript_Bin & strID & "_" & strFunctName & ".csv"
        strDelim = ","

        'Ensure file is delete before new file is exported
        On Error Resume Next
            Kill (strExportTo)
        On Error GoTo 0
        
        'Call ExportToTextFile(strQuery, strExportTo, strDelim, True, False)
    
    End If

    'Clear Tables
    Call DeleteTable(strTempTable)

Proc_Exit:

    '::-- Update Table with Procedure Information --::'
    strEndTime = Format(Now, "mm/dd/yyyy hh:mm:ss")
    strTimeDiff = strEndTime - strStartTime
    Call ADD_RUN_TIMES( _
                        strFunctName, _
                        strStartTime, _
                        strEndTime, _
                        Hour(strTimeDiff) & " hours " & Minute(strTimeDiff) & " minutes " & Second(strTimeDiff) & " seconds", _
                        Switch(strProcError = "", "Success", Not (strProcError = ""), "Failed"), _
                        strProcError _
                       )
    
    If Not rs Is Nothing Then rs.Close
    If Not ws Is Nothing Then ws.Close
    If Not db Is Nothing Then db.Close
    
    Set rs = Nothing
    Set ws = Nothing
    Set db = Nothing
    
    Exit Function

Proc_Err:

    '::-- Rollback Transaction --::'
    If strTFlag = 1 Then ws.Rollback
    
    '::-- Capture VB Error --::'
    strProcError = Err.Description
    
    strSubject = "WARNING : Function '" & strFunctName & "' Failed " & strEnvType
    strBody = Switch(strStep = "", "", Not (strStep = ""), strStep & vbNewLine & vbNewLine) & _
              "VB Error : " & strProcError & vbNewLine & vbNewLine & _
              "Profile : " & CurrentUser() & vbNewLine & _
              "VB Module : " & Application.VBE.ActiveCodePane.CodeModule.Name
    strTo = strMDMSupportEmail
    Call MDM_Routines.Email_Utility(strSubject, strBody, strTo, "", "")
    
    Resume Proc_Exit
    
End Function

Here is my deletetable procedure:

Public Function DeleteTable(strTable As String)
   
    a = Split(strTable, ",")
    For i = 0 To UBound(a)
    
    On Error Resume Next
        CurrentDb.Execute "DROP TABLE " & a(i) & ";"
    Next
    On Error GoTo 0
    
End Function

Any tips would be great, thank you!

[orange]

I don't have an answer for why. However, for debugging I would suggest you add a Debug.Print line(s) to show the tables being dropped. Is it possible the Commit is not being actioned?

After your ws.CommitTrans
Debug.Print strQuery


Inside
Function DeleteTable(strTable As String)
Debug.print "strTable " & strTable

and inside the For loop
Debug.Print "Dropping table " & a (i) & " " & Now

Good luck.

[Welshgasman]

How do you know it is not dropped?
You recreate it immediately afterwards, don't you.?

Surely if it was not dropped, Access would complain when you try and recreate it.?

Why not just delete the records?

[SIMMS7400]

I don't have an answer for why. However, for debugging I would suggest you add a Debug.Print line(s) to show the tables being dropped. Is it possible the Commit is not being actioned?

After your ws.CommitTrans
Debug.Print strQuery


Inside
Function DeleteTable(strTable As String)
Debug.print "strTable " & strTable

and inside the For loop
Debug.Print "Dropping table " & a (i) & " " & Now

Good luck.

Thank you, I will give that a shot!!

[SIMMS7400]

How do you know it is not dropped?
You recreate it immediately afterwards, don't you.?

Surely if it was not dropped, Access would complain when you try and recreate it.?

Why not just delete the records?

Correct, I recreate it after I delete (per the code) but then at the end of the code, I delete it as the final step. What's odd is you're right, if there was an issue I would have seen.

But after the last delete, I'm still able to access the table once the procedure finishes which is strange. And I can confirm the first drop and recreate is happening because I added dummy data to the table and it's refreshed with my source. Just seems strange to me.

[Welshgasman]

I missed that last drop amongst all the code, but why not just delete records instead.?

[Gicu]

I wonder if refreshing the database window would help (mind you it should happen anyway if you close and re-open):

Public Function DeleteTable(strTable As String)
   
    a = Split(strTable, ",")
    For i = 0 To UBound(a)
    
    On Error Resume Next
        CurrentDb.Execute "DROP TABLE " & a(i) & ";"
    Next
    On Error GoTo 0
    
CurrentDb.TableDefs.Refresh    
Application.RefreshDatabaseWindow

End Function

Cheers,

[SIMMS7400]

I wonder if refreshing the database window would help (mind you it should happen anyway if you close and re-open):

Public Function DeleteTable(strTable As String)
   
    a = Split(strTable, ",")
    For i = 0 To UBound(a)
    
    On Error Resume Next
        CurrentDb.Execute "DROP TABLE " & a(i) & ";"
    Next
    On Error GoTo 0
    
CurrentDb.TableDefs.Refresh    
Application.RefreshDatabaseWindow

End Function

Cheers,

So that didn't work. What's odd is this code does the trick:

Public Function DeleteTable(strList As String)
    a = Split(strList, ",")
    For i = 0 To UBound(a)
        If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & a(i) & "'")) Then
            DoCmd.SetWarnings False
            DoCmd.Close acTable, a(i), acSaveNo
            DoCmd.DeleteObject acTable = acDefault, a(i)
            DoCmd.SetWarnings True
        End If
    Next
End Function

But I don't like to use Docmd.

[Welshgasman]

I wonder if refreshing the database window would help (mind you it should happen anyway if you close and re-open):

Public Function DeleteTable(strTable As String)
   
    a = Split(strTable, ",")
    For i = 0 To UBound(a)
    
    On Error Resume Next
        CurrentDb.Execute "DROP TABLE " & a(i) & ";"
    Next
    On Error GoTo 0
    
CurrentDb.TableDefs.Refresh    
Application.RefreshDatabaseWindow

End Function

Cheers,

That worked for me with local tables. The DROP did not remove the table from the list.?

[Minty]

I'd remove the

On Error Resume Next

I suspect that might be hiding an error message you might want to see?
And you should check an object exists before trying to delete it.

[orange]

I agree with Minty. Also, there is no need to have the On Error Resume Next inside the For Loop.

Can you show us some of the output from the Debug.Print statements suggested earlier?

[SIMMS7400]

I agree with Minty. Also, there is no need to have the On Error Resume Next inside the For Loop.

Can you show us some of the output from the Debug.Print statements suggested earlier?

Well this is embarrassing, the reason it wouldn't delete is because my rs (which is my record set) wasn't closed and therefore couldn't delete.

I know my code to IGNORE the errors within the DeleteTable Function aren't "best practice", so I setup my delete logic as such:

    a = Split(strTable, ",")
    For i = 0 To UBound(a)
        If DCount("[Name]", "MSysObjects", "[Name] = '" & a(i) & "'") = 1 Then
            CurrentDb.Execute "DROP TABLE [" & a(i) & "]", dbFailOnError
        End If
    Next

The challenge is, this works fine for the FIRST pass...and then when I go to delete the table as the end of the Function, it doesn't delete. In doing some checking, DCOUNT renders 0 for the second pass, which I have no idea why. It does not seem like it recognizes the created table... So i reverted back to my original logic to ensure its deleted knowing it's not the best of practices however it works.

Thanks everyone!