deleting tables

[sdimaggio]

How can I delete all the tables in a database at one time?

thanks in advance.

 

[tlbroadbent]

Do you want to delete (drop) the tables or just delete teh data? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sdimaggio]

delete the tables

 

[tlbroadbent]

You can use a VBA module like the following.

Sub DeleteUserTables()

Dim tdf As DAO.TableDef

' Enumerate TableDefs collection.
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> &quot;msys&quot; Then
Debug.Print tdf.Name
CurrentDb.TableDefs.Delete (tdf.Name)
End If
Next tdf

Set tdf = Nothing

End Sub
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sdimaggio]

I tried your suggestion on creating a VBA module. I ran into trouble with a warning sign stating:

Compile error
&quot;User defined type not defined&quot;

the following is highlighted (tdf As DAO.TableDef) as the problem.

Any suggestions? Does Access have a built in function that will delet all the tables at once instead of one at a time?

Steve

 

[tlbroadbent]

Check the References to make sure the Microsoft DAO 3.X Object Library is selected. Open the module in design mode, click on Tools | References. Select the latest version listed - probably 3.5 or 3.6. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sdimaggio]

Worked like a charm.

many thanks!