Deleting a Table if It Exists

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

Hi,

Could you tell me how I can delete a table if it exists and not get an error
message if it does not exist?

Thanks,
Charles
 
Hi,

Could you tell me how I can delete a table if it exists and not get an error
message if it does not exist?

Thanks,
Charles
Click to expand...

Just delete it and trap and ignore the error. That's what error handling is
for!
 
Trap the error.

On Error GoTo Err_Handler
DoCmd.DeleteObject "TableDoesNotExist"

Exit_Sub:
Exit Sub
Err_Handler:
If Err = 7874 Then
Else
MsgBox "Error: " & Err.Number & " " & Err.Description
End If
Resume Exit_Sub
Click to expand...

Oops, that should have read:

DoCmd.DeleteObject acTable, "TableDoesNotExist"
 
Sometimes error handling isn't the best solution, say when you want the
procedure to continue even if the table doesn't already exist. It's very
difficult to catch an error and resume the procedure from the same point.

In such a case, you could use a TableExist(TableName) function that will
loop through the tabledefs collection and check for the tabledef.Name
property to see if it matches the passed value. Keep in mind though, that
the tabledefs collection will contain ALL datasources (linked) as well as
local tables. And, if you delete a linked table from the FE, it will delete
the link, not the table in the BE.

The function would look something like this, though I'm not exactly sure if
the tabledef collection called currectly
(Application.CurrentProject.TableDefs?? I forget the location of this
collection off the top of my head...)

Function fTableExist(TableName) As Boolean
Dim td As TableDef
For Each td In Application.CurrentProject.Tabledefs
If td.Name = TableName Then
fTableExist = True
Exit For
End If
Next
Exit Function


So, your code could look like this:

....
....
If TableExist(tblName) = True Then DoCmd.DeleteObject...
....
....



hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
If you're going to call a separate function, you can just have it ignore the
error

Sub DeleteTable(TableName As String)
On Error Resume Next

CurrentDb.TableDefs.Delete TableName
If Err.Number <> 0 Then
Err.Clear
End If

End Sub

Alternatively, use Resume Next when the error raised by deleting a table
that doesn't exist. That will ensure that execution continues at the next
statement.

On Error GoTo Err_Handler
CurrentDb.TableDefs.Delete "TableDoesNotExist"

Exit_Sub:
Exit Sub

Err_Handler:
If Err = 7874 Then
Resume Next
Else
MsgBox "Error: " & Err.Number & " " & Err.Description
Resume Exit_Sub
End If
 
Err_Handler:
If Err = 7874 Then
Resume Next
Else
MsgBox "Error: " & Err.Number & " " & Err.Description
Resume Exit_Sub
End If
Click to expand...

I was not aware that the Resume Next statement can be used inside the error
handler like this, and have the code return to the point of error in the
procedure.

Personally, I would still use a function to check the existence of the table
before trying to delete it. For whatever reason, I seem to have harboured
this notion that whatever can be done to avoid having an error raise in the
first place is generally better than actually letting access raise the error.
But I can also see where a large number of tables can hamper performance if
this is a highly utilized procedure.

To each their own. In any case, I'll make note of the fact that Resume Next
can be used inside the handler to return to the next line of the procedure...
this may come in handy.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Back
Top