Fastest Way For VBA To Delete 4 Local Tables

[GC2010]

I have a database with roughly 100 tables in it, now 75ish are linked SharePoint tables, and the other 25 are local access tables. Can I edit my VBA to ONLY scan the local tables and ignore any other tables?

This is my current syntax but it is slow...

Dim tblNamesArray As Variant

tblNamesArray = Array("Table1", "Table2", "Table3", "Table4")

For Each tbl in tblNamesArray
    On Error Resume Next
    DoCmd.DeleteObject acTable, CStr(tbl)
Next

 

[rpeare]

Dim rst
Dim sSQL As String


sSQL = "SELECT MSysObjects.Name AS table_name, MSysObjects.Type "
sSQL = sSQL & "FROM MSysObjects "
sSQL = sSQL & " WHERE (((Left([Name],1))<>'~') AND ((Left([Name],4))<>'MSys') AND ((MSysObjects.Type) In (1)) AND ((MSysObjects.Flags)=0))"
sSQL = sSQL & "ORDER BY MSysObjects.Name"
'debug.print ssql 'you can print this line if you want to see the compiled SQL statement

Set rst = CurrentDb.OpenRecordset(sSQL)
    If rst.RecordCount <> 0 Then
        Do While rst.EOF <> True
            sSQL = "DROP TABLE [" & rst!table_name & "]"
            CurrentDb.Execute sSQL
            rst.MoveNext
        Loop
    End If
Set rst = Nothing

I am reasonably certain the [type] field is 1 for local tables.

EDIT: please note this will drop *ALL* local tables, not a subset of them.

 

[GC2010]

Dim rst
Dim sSQL As String


sSQL = "SELECT MSysObjects.Name AS table_name, MSysObjects.Type "
sSQL = sSQL & "FROM MSysObjects "
sSQL = sSQL & " WHERE (((Left([Name],1))<>'~') AND ((Left([Name],4))<>'MSys') AND ((MSysObjects.Type) In (1)) AND ((MSysObjects.Flags)=0))"
sSQL = sSQL & "ORDER BY MSysObjects.Name"
'debug.print ssql 'you can print this line if you want to see the compiled SQL statement

Set rst = CurrentDb.OpenRecordset(sSQL)
    If rst.RecordCount <> 0 Then
        Do While rst.EOF <> True
            sSQL = "DROP TABLE [" & rst!table_name & "]"
            CurrentDb.Execute sSQL
            rst.MoveNext
        Loop
    End If
Set rst = Nothing

I am reasonably certain the [type] field is 1 for local tables.

EDIT: please note this will drop *ALL* local tables, not a subset of them.

Ah - I can't drop all local. I only need to drop the 4 specific local tables.

 

[theDBguy]

Dim rst
Dim sSQL As String


sSQL = "SELECT MSysObjects.Name AS table_name, MSysObjects.Type "
sSQL = sSQL & "FROM MSysObjects "
sSQL = sSQL & " WHERE (((Left([Name],1))<>'~') AND ((Left([Name],4))<>'MSys') AND ((MSysObjects.Type) In (1)) AND ((MSysObjects.Flags)=0))"
sSQL = sSQL & "ORDER BY MSysObjects.Name"
'debug.print ssql 'you can print this line if you want to see the compiled SQL statement

Set rst = CurrentDb.OpenRecordset(sSQL)
    If rst.RecordCount <> 0 Then
        Do While rst.EOF <> True
            sSQL = "DROP TABLE [" & rst!table_name & "]"
            CurrentDb.Execute sSQL
            rst.MoveNext
        Loop
    End If
Set rst = Nothing

I am reasonably certain the [type] field is 1 for local tables.

EDIT: please note this will drop *ALL* local tables, not a subset of them.

Hi. Perhaps you could use the above approach and simply check for any exceptions to avoid deleting any local tables you want to keep.


Edit: If you only want to delete 4 out of 25 local tables, then it might be easier just to name them in your code, rather than loop through all 25 of them to find the four to delete.

 

[gemma-the-husky]

with DAO (although this isn't checking the record count)

dim tdf as tabledef
for each tdf in currentdb.tabldefs
    if tdf.connect="" then
         'a local table
          'to delete it
         currentdb.tabledefs.delete tdf.name        
    end if
next

 

[GC2010]

Hi. Perhaps you could use the above approach and simply check for any exceptions to avoid deleting any local tables you want to keep.


Edit: If you only want to delete 4 out of 25 local tables, then it might be easier just to name them in your code, rather than loop through all 25 of them to find the four to delete.

So above this line ---

sSQL = "DROP TABLE [" & rst!table_name & "]"

There I would add my conditional For Each statement since we have explicitly stated to check local tables only at this point?

 

[jdraw]

If you know the names of the tables you wish to delete, you could put the names into an array, as below:

Sub removeTables()
10        On Error GoTo removeTables_Error
          Dim i As Integer
          Dim TableNames(1) As String
20        TableNames(0) = "TblX10"
30        TableNames(1) = "TblX11"
40        For i = 0 To UBound(TableNames)
50            CurrentProject.Connection.Execute "DROP table " & TableNames(i), dbFailOnError
              Debug.Print "Dropped " & TableNames(i)
60        Next i
70        On Error GoTo 0
80        Exit Sub

removeTables_Error:

90        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure removeTables, line " & Erl & "."

End Sub

I do not use Sharepoint.

 

[theDBguy]

So above this line ---

sSQL = "DROP TABLE [" & rst!table_name & "]"

There I would add my conditional For Each statement since we have explicitly stated to check local tables only at this point?

Hi. Yes, you can. But, like I said in my edit above, this would make sense if you have a lot of table to delete and only a few to exclude. But in your case, it's the opposite, so I was thinking it would be easier to just name them all. But it's all up to you. Cheers!

 

[GC2010]

Hi. Yes, you can. But, like I said in my edit above, this would make sense if you have a lot of table to delete and only a few to exclude. But in your case, it's the opposite, so I was thinking it would be easier to just name them all. But it's all up to you. Cheers!

Wait - I thought that is what you meant...

Or are you referencing an approach like @jdraw said above?

 

[theDBguy]

Wait - I thought that is what you meant...

Or are you referencing an approach like @jdraw said above?

No, I was saying something more along these lines. To delete only four specific tables:

DoCmd.DeleteObject acTable, "Table1"
DoCmd.DeleteObject acTable, "Table2"
DoCmd.DeleteObject acTable, "Table3"
DoCmd.DeleteObject acTable, "Table4"

Done, no need for a loop and no need to check for table names. Of course, it's not as flexible as the other suggested approaches, but I was just saying it's an easy option for now.

 

[rpeare]

yeah if you're only dropping four tables, and it's the same four tables each time I'd just execute four drop table statements. Adding items to an array is unnecessary.

 

[jdraw]

Here is a link (it used to be a sticky here??) to an include/exclude approach from Bob Larson. It may not apply to you specific need, but depending on the numbers involved, it could be.
If it's really 4 tables, just do the deletes.
If it's 10-20 or more, give the link consideration.

 

[The_Doc_Man]

I'll go one step farther down this little garden path.

If it is always the same tables, whether 1 or 4 or 20, do the deletes by name. You can use cut (once)/paste (20 times) on

DoCmd.DeleteObject acTable, ""

and then edit in the however many constant table names are involved, one per line in the quoted string. But if even now and then there is some variation, then you need something to indicate which tables are candidates for the current iteration and THAT is where a loop becomes an issue.