Locate a table using VB

[Max]

I need to find if a given table, MyTableB, exists in the database. Is there a
function, such as IsTable(MyTableB) or Exist(MyTableB) that I can use to make
this determination? I have searched Help and KB with no results.
Please help.
Thanks in advance...

 

[Steve]

Look at TableDef in the Help file.

Steve
(e-mail address removed)

 

[Max]

Took me a while to find the correct language to use, but here it is:

Dim tdfLoop As TableDef
For Each tdfLoop In CurrentDb().TableDefs
If tdfLoop.Name = "MyTableB" Then
DoCmd.DeleteObject acTable, "MyTableB"
End If
Next tdfLoop

So thanks for the point in the right direction.
Max

 

[Steve]

Hi Max,

Way to go!!!

Steve

Took me a while to find the correct language to use, but here it is:

Dim tdfLoop As TableDef
For Each tdfLoop In CurrentDb().TableDefs
If tdfLoop.Name = "MyTableB" Then
DoCmd.DeleteObject acTable, "MyTableB"
End If
Next tdfLoop

So thanks for the point in the right direction.
Max

 

[Dale Fye]

Here is a quicker way. All you have to do is refer to the table name and try
to access one of its properties. If it generates an error, you know the
table doesn't exist.

Public Function TableExists(Tablename as string) as boolean

dim dtCreated as date
On error goto ProcError

dtCreated = currentdb.tabledefs(tablename).datecreated

TableExists = true

Exit Sub

ProcError:
if err.number = 3265 then 'item not found in collection
TableExists = false
else
msgbox "Error encountered in TableExists function"
TableExists = false
endif

End function

Then use:

Public Sub DeleteIfExists(Tablename as string)

if TableExists(tablename) then
docmd.deleteobject actable, tablename
endif

End sub