DeleteObject Using Wildcard

[TryTryAgain]

Does anyone know how to set up a DeleteObject command using a wildcard? Example - I want to delete all the tables in a database that have "copy" in the table name. Thanks!

 

[Remou]

Hi
You could loop through the tables (CurrentDB.TableDefs) and if the table name is like "*copy*", delete it.

 

[AlanJordan]

You might find these pieces of code helpful. They work for me.

Function KillTable(strTableName As String)
Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
    'debug.print qdf.Name
    If tdf.Name = strTableName Then
        dbs.TableDefs.Delete tdf.Name
    End If
Next tdf


End Function

Function KillTmpQuery()
Dim qdf As QueryDef
Dim dbs As Database
Set dbs = CurrentDb

For Each qdf In dbs.QueryDefs
    'debug.print qdf.Name
    If qdf.Name = "qryTmp1" Then dbs.QueryDefs.Delete qdf.Name
Next qdf


End Function

Function UnDeleteTable(Optional sName As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sTable As String
Dim sSQL As String
Dim sMsg As String

    If IsMissing(sName) Then sName = "RestoredTable"
    If Len(sName) = 0 Then sName = "RestoredTable"
    
    Set db = CurrentDb()

    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) = "~tmp" Then
           sTable = tdf.Name
           sSQL = "SELECT [" & sTable & "].* INTO " & sName
           sSQL = sSQL & " FROM [" & sTable & "];"
           
           db.Execute sSQL
           
           sMsg = "A deleted table has been restored as " & sName
           MsgBox sMsg, vbOKOnly, "Restored"
           GoTo Exit_Undelete
        End If
    Next
    
    ' If the code has fallen to this point, then no deleted
    ' tables exist in the catalog and none are recoverable.
    MsgBox "No Recoverable Tables Found", vbOKOnly, "Not Found"

Exit_Undelete:
     Set db = Nothing
     Exit Function
     
Err_Undelete:
     MsgBox Err.Description
     Resume Exit_Undelete

End Function

I hope that they help. P.S. I'm leaving for the day, and won't be here to respond. If you still have trouble, perhaps someone else can jump in.