Unable to Delete Table in an Endless Loop

[Hashiru]

Hi All,

I want to ensure that there is no table with the Name CurrentData, so I want to delete that table and create one with the same name through import using DoCmd.RunSQL. However, I am having continuous loop. Can anyone help me on how to correctly delete the table CurrentData.

Here is the code I have thus far. The code endlessly loop on the highlight lines.

 Option Compare Database
Private Sub CmdReport_Click()
DeleteTable
Dim MyFile As FileDialog
Set MyFile = Application.FileDialog(msoFileDialogFilePicker)
  'Browse for the Datasource and set the title of the dialog box.
    With MyFile
        
    .Title = "Browse for the relevant Report "
        If .Show = True Then
         ' Assign the file to a variable Reportbk.
            accessfilepath = MyFile.SelectedItems.Item(1)
            Else
               MsgBox "You clicked Canncel in the file dialog box.", , "Canceling the data extraction process"
                Exit Sub
        End If
    End With
Dim StrFileName As String, StrPath As String
StrFileName = Mid(accessfilepath, InStrRev(accessfilepath, "\", -1) + 1, Len(accessfilepath) - InStrRev(accessfilepath, "\", -1))
StrPath = "DATABASE=" & Left(accessfilepath, InStrRev(accessfilepath, "\", -1) - 1)
DoCmd.SetWarnings False
    'DoCmd.tab
    DoCmd.RunSQL "SELECT * INTO CurrentData FROM [Text;" & StrPath & ";HDR=Yes]." & StrFileName
    'DoCmd.RunSQL "SELECT * INTO CurrentData FROM [Text;DATABASE=C:\Users\C033732\Desktop\WMATA Report Templates;HDR=Yes].DennisReport.csv"
DoCmd.SetWarnings True
End Sub

Sub DeleteTable()
Dim conn As ADODB.Connection
Dim strTable As String
    On Error GoTo ErrorHandler
    Set conn = CurrentProject.Connection
    strTable = "CurrentData"
    conn.Execute "DROP TABLE " & strTable
    Application.RefreshDatabaseWindow
ExitHere:
conn.Close
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    If Err.Number = -2147217900 Then
        DoCmd.Close acTable, strTable, acSavePrompt
    Resume 0
    Else
        MsgBox Err.Number & ":" & Err.Description
        Resume ExitHere
    End If
End Sub

 

[stumac]

MSysObjects is a system table which holds details of all objects in the database - 'Name' is a column.

What the Dcount does is check if an object with the Name of the table you are trying to delete exists before attempting to delete.