Check for existing table in Access

[Pamy]

Hi,

I need to check for existing table in Access. How should i go about doing this?

 

[danvlas]

You can use the TableDefs collection:

Function TableExists(TableName As String) As Boolean
Dim tdf As TableDef
On Error GoTo ErrHandler
Set tdf = CurrentDb.TableDefs(TableName)
TableExists = True
Finish:
Set tdf = Nothing
Exit Function
ErrHandler:
TableExists=False
Resume Finish
End Function

Or you can use a recordset based on MSysObjects table:

Function TableExists(TableName As String) As Boolean
Dim rst As DAO.Recordset
Dim strSQL As String
On Error GoTo ErrHandler

strSQL = "SELECT MSysObjects.Name " _
& "FROM MsysObjects " _
& "WHERE [MSysObjects].[Name] = '" & TableName & "' AND " _ & "[MSysObjects].[Type]=1;"

Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
TableExists = True
Else
TableExists = False
End If
rst.Close
Finish:
Set rst = Nothing
Exit Function

ErrHandler:
'you shouldn't get here, but...
MsgBox Err.Number & Err.Description
Resume Finish
End Function

The code is written in DAO model, for ADO you'll have to modify it a little bit.

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com