Drop all tables

[Randy]

Hello,
I know you can drop individual tables with the Drop statement...but is there
any way to drop all tables without specifying the table names?

Thanks

 

[Guest]

Here is some quickly written untest code that should do it - I'd try it on a
copy first to make sure as I didn't have time to test it.

Public Sub dropAll()
Dim conn As ADODB.Connection
Dim tblSchema As ADODB.Recordset
Dim strTbl As String
Dim strList As String

Set conn = CurrentProject.Connection

Set tblSchema = conn.OpenSchema(adSchemaTables)
Do While Not tblSchema.EOF
strTbl = tblSchema("TABLE_NAME")
'//Make sure we don't try to drop system tables
If Left(strTbl, 4) <> "Msys" Then
Docmd.RunSQL "Drop Table [" & strTable & "];"
End If
tblSchema.MoveNext
Loop

Set conn = Nothing
End Sub

-Steve Huff
http://www.huffs.us