checking for existence of table in VBA using SQL 2

[presence76]

I have a problem. I have a table that is created in an ACCESS DB that runs daily. This table is deleted at the beginning of the next run. In certain situations, the table does not exist. I have been trying to test for that situation so I can continue processing without error but have been unsuccessful. Here is what I have done:

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.

 

[p27br]

you should put the on error resume next before the sql statement.

 

[presence76]

Thanks for the quick response.

As a general rule, I don't use error bypassing so On Error Resume Next was code I had never used - consequently, my erroneous placement of it. I tested it and it worked like a charm. Thanks a bunch.

 

[evalesthy]

If you want a broader capability create a module that you can call whenever you need to know if a table exists:

Module:

Function IsTable(DbName As String, TName As String) As Integer
Dim db As DAO.Database
Dim Found As Integer
Dim Test As String
Const Name_Not_In_Collection = 3265

Found = False

On Error Resume Next
'If the database name is empty..
If Trim$(DbName) = "" Then 'If db name not specified in function call isTable("",xx)
'...set the db to current db
Set db = CurrentDb()
Else
'otherwise set db to specified open database
Set db = DBEngine.Workspaces(0).OpenDatabase(DbName)
'see if an error occurred
If Err Then
MsgBox "Could not find database to open: " & DbName, vbOKOnly, "Not Found!"
IsTable = False
Exit Function
End If
End If

'see if the name is in the Tables collection
Test = db.TableDefs(TName$).Name
If Err <> Name_Not_In_Collection Then Found = True

db.Close
IsTable = Found


End Function

The in code just use:

'check TO SEE IF TABLE EXISTS
If IsTable("", "YourTableNameHere") = True then
'do something
Else
'do something else
End If

I got this off the web sometime ago and cannot credit the source - but it has come in handy many times.

 

[rgbanse]

Try this
If DCount("*", "MSysObjects", "name = 'YourTableName'") = 0 Then MakeYourTable
RGB