Unable to Delete Table in an Endless Loop

Hashiru

Active Member
Joined
May 29, 2011
Messages
286
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.

Rich (BB code):
 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

Rich (BB code):
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
 
Last edited:

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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.
 
Last edited:

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,080
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top