test for a table existence

[Koni Kogan]

How can I do this in Access? I need to be able to figure out if the
table exists before executing drop table command in a DTS Package.

Thanks,
Koni.

 

[[MVP] S.Clark]

There are several ways.
1. Query msysObjects for the name
2. Try to open a recordset based on the table. If it fails, there's no
table.
3. Step through the tabledefs looking for the name.

All done with VBA code.

 

[Koni Kogan]

There are several ways.
1. Query msysObjects for the name
2. Try to open a recordset based on the table. If it fails, there's no
table.
3. Step through the tabledefs looking for the name.

All done with VBA code.

Thanks much! Could you or anyone provide examples? How about just
using sql?

 

[Jeff Conrad]

' Code Start
Public Function funcTableExists(strTable As String) As Boolean
On Error GoTo ErrorPoint

' This function will check to see if a table exists within the current database
' Similar to IsLoaded function it will return True or False

Dim db As DAO.Database
Dim doc As DAO.Document

Set db = CurrentDb()

With db.Containers!Tables
For Each doc In .Documents
If doc.Name = strTable Then
funcTableExists = True
End If
Next doc
End With

ExitPoint:
On Error Resume Next
Set db = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Function
' Code end

To call the function:

If funcTableExists("MyTableName") = True Then
' Table exists in the current database
Else
' Table does not exist
End If