Delete Table Function Not Working

[Guest]

I'm having a problem with the following function. The data is written to a
temp table, formatted and then exported to Excel. The function is supposed
to look for the temp table first and delete it if found. Sometimes it does,
sometimes it doesn't. Stepping through it, the IF line appears to see the
table (True=True), but sometimes it jumps to the "end if" line. This is
frustrating. Where did I go wrong?

Public Function fDeleteTempTable(strTableName As String)
On Error GoTo HandleErr

Dim lngTblCount As Long
Dim lngTblNdx As Long
Dim tdfs As TableDefs

Set tdfs = CurrentDb.TableDefs

If (IsObject(tdfs(strTableName))) = True Then
DoCmd.DeleteObject acTable, strTableName
End If


ExitHere:
Set tdfs = Nothing
Exit Function

HandleErr:
If Err.Number = "7874" Then
Resume ExitHere
ElseIf Err.Number = "3265" Then
Resume ExitHere
Else
MsgBox "Err#" & Err.Number & ": " & Err.Description
Resume ExitHere
End If

End Function


Thanks

 

[Guest]

You should read the VB Help info on the IsObject function. It appears you
are not using it the way it is described and may be getting unreliable
results.

I would suggest you add this function to a standard module and use it instead.

Public Function TableExists(strTableName As String) As Boolean
Dim tdfs As TableDefs
Dim tdf As TableDef
Set tdfs = CurrentDb.TableDefs
For Each tdf In tdfs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Set tdf = Nothing
Set tdfs = Nothing

End Function