checking for existence of access table using SQL in VBA

[presence76]

I have a problem. I have an ACCESS DB that I run daily. One of the things it does is creates a table that is used temporarily. Then, on the next run, I delete it before I begin processing. I have a new situation where this table might not exist (such as the first day of a new install) and I do not want the DB to abend in the middle of a process just because this table does not exist.

I have tried several different techniques.

Here is the basic command I use to delete the table:

DoCmd.RunSQL "DROP TABLE tbltracerfinal;"

I then changed it to :

DoCmd.RunSQL "DROP TABLE tbltracerfinal;"
On Error Resume Next

This does not work as VB kicks an SQL error statement before it interprets the next line (ON Error....)

I tried several different versions of SQL code that I thought would work like:

'DoCmd.RunSQL "If (tblTracerfinal) then DROP TABLE tblTracerfinal;"

This has faulty SQL code.

Does anyone know how to check for the existence of a table using SQL in a VBA environment?
OR
Does anyone know how I could do it using VBA code???

Any help would be greatly appreciated. Thanks.

 

[presence76]

I found it.

On Error Resume Next needs to be placed on the line BEFORE the line it is being applied to.

 

[bat17]

The resume next needs to come before the error! Try
On Error Resume Next
DoCmd.DeleteObject acTable, "tblTracerfinal"

HTH

Peter

 

[Norie]

Try the following function:

Function IsTable(tblName As String) As Boolean
' returns true if there is a table called tblName

Dim db As Database
Dim tdf As TableDef

    Set db = CurrentDb

    For Each tdf In db.TableDefs
        IsTable = (tdf.Name = tblName)
        If IsTable Then Exit Function
    Next
    
    Set tdf = Nothing
    Set db = Nothing
    
End Function