Fastest Way For VBA To Delete 4 Local Tables (1 Viewer)

GC2010

Registered User.
Local time
Today, 02:55
Joined
Jun 3, 2019
Messages
120
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...
Code:
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

Registered User.
Local time
Today, 02:55
Joined
Sep 15, 2016
Messages
18
Code:
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

Registered User.
Local time
Today, 02:55
Joined
Jun 3, 2019
Messages
120
Code:
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

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,358
Code:
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

Super Moderator
Staff member
Local time
Today, 09:55
Joined
Sep 12, 2006
Messages
15,614
with DAO (although this isn't checking the record count)

Code:
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

Registered User.
Local time
Today, 02:55
Joined
Jun 3, 2019
Messages
120
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 ---
Code:
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

Super Moderator
Staff member
Local time
Today, 05:55
Joined
Jan 23, 2006
Messages
15,364
If you know the names of the tables you wish to delete, you could put the names into an array, as below:
Code:
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

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,358
So above this line ---
Code:
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

Registered User.
Local time
Today, 02:55
Joined
Jun 3, 2019
Messages
120
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

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,358
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:

Code:
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

Registered User.
Local time
Today, 02:55
Joined
Sep 15, 2016
Messages
18
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

Super Moderator
Staff member
Local time
Today, 05:55
Joined
Jan 23, 2006
Messages
15,364
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

Immoderate Moderator
Staff member
Local time
Today, 04:55
Joined
Feb 28, 2001
Messages
27,001
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

Code:
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.
 

Users who are viewing this thread

Top Bottom