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:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,954
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
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
The error handling uses a Resume statement, which could be a cause for a loop.
 

stumac

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

Code:
CurrentDb.TableDefs.Delete (strTable)

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

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

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

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

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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):
Code:
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
 
Last edited:

stumac

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

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

Active Member
Joined
May 29, 2011
Messages
286
Thanks Ranman256 for letting me know another way on how to delete a table.
@stumac what Name and MSysObjects represents?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

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