Deleting Tables

[aziz rasul]

I am trying to delete all the tables in my database, except for the system tables of course. How do I amend the code below so that ignores the system tables?

At the moment, it does not understand "MS*".
Private Sub Command88_Click()
Dim x As Integer
Dim dbs As Database
Dim tdfcount As Integer
Dim index As Integer

Set dbs = CurrentDb

tdfcount = dbs.TableDefs.Count

For index = 0 To tdfcount - 1
If dbs.TableDefs(index).Name = "MS*" Then
Else
DoCmd.DeleteObject acTable, dbs.TableDefs(index).Name
End If
Next index

End Sub

 

[ritchieroo]

Use this...

dbs.TableDefs(index).Name Like "MSys*"

also safer to test for non-system tables like this...

dbs.TableDefs(index).Attributes And dbSystemObject = 0

 

[aziz rasul]

I had tried

If dbs.TableDefs(index).Name = Like "MSys*"

and it didn't work.

As you quite rightly said, it should have been

If dbs.TableDefs(index).Name Like "MSys*"

Thanks.

 

[ritchieroo]

see editted version of previous reply for better way to detect system objects

 

[aziz rasul]

What exactly does

dbs.TableDefs(index).Attributes And dbSystemObject = 0

do.

 

[raskew]

The following code would delete all non system tables in an application.

To prevent that from inadvertently occurring, I've remarked out the code
that will do the damage and replaced it with debug.print displays, i.e.

If thisrel.Table = tName Or thisrel.ForeignTable = tName Then
   Debug.Print tName & " | " & thisrel.Name
   [b] 'dbs.Relations.Delete thisrel.Name[/b]
End If

Debug.Print tName & " will be deleted"
[b]'docmd.SetWarnings False
'docmd.DeleteObject acTable, tName
'docmd.SetWarnings True[/b]

Run it initially against a duplicate/sample database, then open the debug window
to view a printout of what would have occurred. Once you're confident
with the operation, remove the remarks and then rerun the code to delete
all of the tables.

Function DeleteTableTest2()
' This procedure deletes specified tables and any
' existing relationships the tables are participating
' in.
' Used inappropriately, it will have a devastating
' effect on an application.

Dim dbs As DATABASE, test As String
Dim td As TableDef, tName As String
Dim found As Boolean, thisrel As Relation

Set dbs = CurrentDb

On Error Resume Next

For Each td In dbs.TableDefs
   If Left(td.Name, 4) <> "MSys" Then
     tName = td.Name
    '**********
    'Does table tName exist?
    'If true, delete it;
    '**********
    found = False
    test = dbs.TableDefs(tName).Name
    If Err <> 3265 Then
       found = True
       '**********
       ' Since the delete action will fail if the
       ' table is participating in any relation, first
       ' find and delete existing relations for table.
       '**********
       For Each thisrel In dbs.Relations
          If thisrel.Table = tName Or thisrel.ForeignTable = tName Then
             Debug.Print tName & " | " & thisrel.Name
             'dbs.Relations.Delete thisrel.Name
          End If
       Next thisrel
       '**********
       ' Now, we're ready to delete the table.
       '**********
       Debug.Print tName & " will be deleted"
       'docmd.SetWarnings False
       'docmd.DeleteObject acTable, tName
       'docmd.SetWarnings True
    End If
  End If
Next td
dbs.Close
Set dbs = Nothing
End Function

 

[edubbelaar]

Many thanks raskew, this code works fine!