delete table

[ghudson]

Is there a better way (less code) to delete a table IF it exists? This is what I am using...

Dim t As Object
For Each t In CurrentDb.TableDefs
If t.Name = "TestTable" Then DoCmd.DeleteObject acTable, "TestTable"
Next t

Thanks in advance for your help and suggestions!

 

[ritchieroo]

How about this?

Public Sub DeleteTable(ByVal TableName As String)
  On Error Resume Next
  If Not IsError(DBEngine(0)(0).TableDefs(TableName)) Then _
    DBEngine(0)(0).TableDefs.Delete TableName
End Sub

 

[ghudson]

I try not to use that error handler for I could use this which is similar to your code...

On Error Resume Next
DoCmd.DeleteObject acTable, "TestTable"

I was trying for a simple one liner using an if statement like

If "TestTable" exists then DoCmd.DeleteObject acTable, "TestTable"

Is this possible?

Thanks for your help and suggestions!

 

[ritchieroo]

How about this?

  If CurrentDb.OpenRecordset("SELECT 1 FROM MSysObjects WHERE Type=1 AND Flags=0 AND Name='Table1'").RecordCount = 1 Then
    CurrentDb.Execute "DROP TABLE Table1"
  End If

 

[ghudson]

Exactly what I was looking for! Thank You!

If CurrentDb.OpenRecordset("SELECT 1 FROM MSysObjects WHERE Type=1 AND Flags=0 AND Name='Table1'").RecordCount = 1 Then DoCmd.DeleteObject acTable, "Table1"