CODEWITHSUNDEEP
ms-accessvba
TJ15
TJ15

Reputation: 72

Drop All Tables Except One in MS Access (2007-2013)

I'm trying to delete all tables except for one in Access. This is done locally, so there are no linked tables and no relationships. The table I am to keep is 'Log' (historical record).

I three tables and two import-error tables I aim to drop. This is a monthly process and the import error tables may be named differently each month, so a few daisy-chained DROP TABLE "Table1_ImportErrors" queries won't necessarily work.

The code here doesn't seem to apply since I can only really name the table I want to keep (this designates tables to drop by name - I won't have that in the case of import errors).

What I currently have is:

-dbo.Table1

-dbo.Table2

-dbo.Table3

-dbo.T2Sheetname_ImportErrors

-dbo.T3Sheetname_ImportErrors

-dbo.Log

-9 Queries

-4 Macros

-2 Modules

I want to keep all queries, macros, modules, and dbo.Log.

I'm just learning VBA, but unclear on what to do here. Open to using a query here as well or other approaches. I appreciate any input and am glad to answer any questions.

EDIT: The following worked for me.

Private Sub
DoCmd.DeleteObject acTable, "Table1"
DoCmd.DeleteObject acTable, "Table2"
DoCmd.DeleteObject acTable, "Table3"

Dim tdf as TableDef
    For Each tdf In CurrentDb.TableDefs
        If Right(tdf.Name, 12) = "ImportErrors" Then
            DoCmd.DeleteObject acTable, tdf.Name
        End If
    Next tdf
End Sub

Upvotes: 1

Views: 420

Answers (1)

donPablo
donPablo

Reputation: 1959

Here are the results of googling some phrases like "msaccess for all tables" and "msaccess vba delete table". Use Google, it is your friend.

Depending of what the msgbox sez, you may need to filter on "dbo.Log" or on "Log".

'From  https://stackoverflow.com/questions/17555174/how-to-loop-through-all-tables-in-an-ms-access-db 

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        ' ignore system and temporary tables
        If Not (tdf.name Like "MSys*" Or tdf.name Like "~*") Then
            If tdf.name <> "Log" Then
            MsgBox("deleting " & tdf.name)
            '  from https://stackoverflow.com/questions/15945297/what-is-the-difference-between-docmd-deleteobject-actable-vs-drop-table
            'DoCmd.DeleteObject acTable, tdf.name
        End If
        End If
    Next
    Set tdf = Nothing
    Set db = Nothing

Upvotes: 2

Related Questions