test for a table existence

  • Thread starter Thread starter Koni Kogan
  • Start date Start date
K

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.
 
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.
 
[MVP] S.Clark said:
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.
Click to expand...
Thanks much! Could you or anyone provide examples? How about just
using sql?
 
' 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
 
Back
Top