How to determine whether a table exists

[patwadd]

How do I determine in VBA whether a table exists? I use a temporary table in generating online sales, and need to determine whether it already exists before I try to delete it (if it doesn't exist I get an error). I've tried using an On Error Go To line immediately following the delete command, but the module aborts on the delete command line without getting as far as the error handling line.

Under normal circumstances the temporary table will be deleted on completion of the sale and created new at the start of a sale. However, if the User should just switch his PC off the temporary table will still be there, containing data from the preceding sale. Incidentally, the table HAS to have a name, as separate tables are created for each database user.

 

[Richie]

This article in the MSKnowledge base may be of some help Q113549

 

[Atomic Shrimp]

I'm sure I shouldn't be saying this, but in similar circumstances, I always just put:

On Error Resume Next
Just before the deletion line and:

On Error Goto 0
Just after it, and I've not had any problems.

(just don't tell anyone I said so, OK?)

Mike

 

[JMARZ]

The following is code that I used for a similiar situation:

'// Search for temp table, tblTempFound. If it is found the tabledefs collection delete it so we do not get
'// an error later when we try to build it.
With dbsFCA
For Each tbld In .TableDefs
If tbld.Name = "tblTempFound" Then
.TableDefs.Delete ("tblTempFound")
End If
Next tbld
End With

Jeff