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

 

[ranman256]

Why would you delete a table?
normally you empty tables.

but you don't use SQL to delete a table,
delete the tabledef.

currentdb.tabledefs("myTable").delete

 

[xenou]

The error handling uses a Resume statement, which could be a cause for a loop.

 

[stumac]

I agree you should limit deleting and creating tables - you will end up with DB bloating - there are reasons I have used it for though.

Both the SQL and deleting the table def will work although the syntax for deleting tabledef is:

CurrentDb.TableDefs.Delete (strTable)

Both will also error out if the table doesn't exist - check for it first, one way:

If DCount("[Name]", "MSysObjects", "[Name] = '" & strTable & "'") = 1 Then CurrentDb.TableDefs.Delete (strTable)

 

[xenou]

That is what I was thinking too - though I wouldn't necessarily worry about bloat - I have seen warnings a-plenty I have never actually encountered a problem with this. Whatever dangers might exists deleting tables they are probably the same or equivalent in risk level to deleting rows.

But for MSAccess whether deleting tables or just deleting rows, you do need to maintain the database with regular compact and repair actions to free up space.

And of course for all databases at least some minimal level of backup plan is a must.

 

[stumac]

I have found bloat an issue when in the front end - i.e. using temp tables to increase speed and reduce network traffic for high volumes of data entry (talking around 20 users updating ~3k records each per hour over VPN). The database grows from 1mb to around 50mb over 3 hours or so and can become unstable.

I found switching to append/delete queries removed the problem.

 

[xenou]

Fair enough. I personally wouldn't use MSAccess in that case.

Note:
running a test (10,000 iterations of copying a medium sized or rather smallish table of 4 columns and 1,000 rows) verifies your result but not overwhelmingly so ...

DB grows to 400,000 KB from 1000 KB with create/delete to a table
DB grows to 321,000 KB from 1000 KB with delete/insert to a table.

so both are problematic if bloat is a problem, but one looks to be better than the other (perhaps significantly so, depending on what that 80,000 KB means in a given context).

My code (for what it's worth):

Sub foox()

Dim s As String
Dim i As Long

For i = 1 To 10000
    
    s = "select * into Table11 from Table1"
    DoCmd.SetWarnings False
    DoCmd.RunSQL s
    DoCmd.SetWarnings True
    
    On Error Resume Next
        CurrentDb.TableDefs.Delete ("Table11")
    On Error GoTo 0
    
Next i

End Sub

Sub fooz()

Dim s As String
Dim i As Long

For i = 1 To 10000
    
    s = "insert into Table12 select Supplier, Spend, YR, F1 from Table1"
    DoCmd.SetWarnings False
    DoCmd.RunSQL s
    DoCmd.SetWarnings True
    
    s = "delete * from Table12"
    DoCmd.SetWarnings False
    DoCmd.RunSQL s
    DoCmd.SetWarnings True
    
    
Next i

End Sub

 

[stumac]

I originally built the back end on Sql server but had to downgrade due to an It department refusing access to it, taking months to allow any changes etc etc. It works perfectly on access because technically there isn't usually many concurrent users with the data being copied into the FE updated and copied back. I ran tests before I went ahead and after 100k iterations there was no backend bloat and no corruption issues.

Interesting stats on delete table v delete query, I found it to be a much bigger gap, though dont have the stats to hand.

 

[Hashiru]

Thanks everyone for the great inputs. I was not in the office today and so I will try all suggestion above and see what works. I really appreciate for all these comments as I have series of projects with these files all will be Pivot table reports.

 

[Hashiru]

Thanks Ranman256 for letting me know another way on how to delete a table.
@stumac what Name and MSysObjects represents?