How to determine if table exists in VBA

[Tony Girgenti]

Is there a way to determine if a table exists in the database using VBA ?

I want to be able to delete a table once determined it exists.

Thanks,
Tony

 

[Michel Walsh]

Hi,



CurrentDb.Execute "DROP TABLE tableName"


The table may be not "deletable" (because of some DRI), if so, it would
still be there, but otherwise, just delete it, even if it is not "there"...


Public Function IsTableExist(ByVal tableName As String) As Boolean
Dim db As Database : Set db=CurrentDb
Dim t As TableDef

For each t in db.Tabledefs

If tableName = t.Name then
IsTableExist=true
Exit Function

End If
next t

End Function




Hoping it may help,
Vanderghast, Access MVP

 

[Paul Overway]

The easiest way to do this would be to just delete the table and ignore the
error generated if the table doesn't exist, i.e.,

Dim db As Database

On Error GoTo err_Proc:

Set db = CurrentDb()

db.TableDefs.Delete "sometable"

Exit Sub

err_Proc:

Select Case Err.Number
Case 3265 'Table
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, Err.Source
End Select