Deleting tables

[jimmy_p84]

Hi i have a lot of linked table that i want to delete I have the macro below that deletes one at a time but i have to put in the name is there a way to delete all the ones that end in _cfd_posting as before this name there is a six digit number but these r all different is this possible

Sub delete()
DoCmd.DeleteObject acTable, "060204_cfd_postings"
End Sub

 

[john471]

All care / no responsibility - I haven't used this, I just knocked it together for you... be sure to create a back-up copy of your database and test it carefully before you use it...

Public Sub DeleteAllTablesBySuffix(szSuffix As String)
    On Error GoTo DeleteAllTablesBySuffix_err
    Dim nCounterAll As Integer
    Dim nCounterFound As Integer
    Dim nCounterDelete As Integer
    Dim szTableName() As String
    
    CurrentDb.TableDefs.Refresh
    nCounterFound = 0
    For nCounterAll = 0 To CurrentDb.TableDefs.Count - 1
        If CurrentDb.TableDefs(nCounterAll).Name Like "*" & szSuffix Then
            If Not CurrentDb.TableDefs(nCounterAll).Name Like "MSys*" Then
                'Never delete System Tables
                ReDim Preserve szTableName(0 To nCounterFound)
                szTableName(nCounterFound) = CurrentDb.TableDefs(nCounterAll).Name
                nCounterFound = nCounterFound + 1
            End If
        End If
    Next nCounterAll
    
    If nCounterFound > 0 Then
        For nCounterDelete = 0 To nCounterFound - 1
            CurrentDb.TableDefs.Delete szTableName(nCounterDelete)
        Next nCounterDelete
    End If
    
DeleteAllTablesBySuffix_exit:
    CurrentDb.TableDefs.Refresh

DeleteAllTablesBySuffix_err:
    MsgBox "An Error occurred:" & vbCrLf & "#" & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbExclamation, "Oooops"
    Resume DeleteAllTablesBySuffix_exit
End Sub

You would do something like
Call DeleteAllTablesBySuffix("_cfd_postings")
and hey-presto ALL tables whose name ends in _cfd_postings would be deleted.

HTH

Regards

John