How do I confirm that a particular TABLE exists in VBA code? 2

[craiglauren]

I need to confirm if a table of name "CLIENT" exists with my current database (currentdb), but can't find a way.
I'm sure I probably end up using TableDefs but don't know how...
Any ideas?

 

[MichaelRed]

A) Loop through the tabledefs. Check the tabledef.name = your (desired/required) Name. If Match then, set a flag and exit the tabledefs loop. At exit of loop, check flag, If set then table exists else it doesn't.


B) Shorter/messier. Set error trap to specific label (NoTable. At error trap. Set flag (NoTable = True)
Return (to code) Reset Error Trap. Check Flag 'NoTable' It set, table doesn't exist, else it is there for you ....

In a 'large' database "B)" may actually be faster, how ever it requires somewhat more care and attention, as failure to set/reset the error trap (properly) can cause a host of other problems.

A) is probably the prefered approach. It is not that difficult, and is 'pratically' right out of one (or more) of the Ms. Access help topics. Of course you do not need to do the create a new table, so just omit that part. You also need to set the data base to your own database(typically CurrentDB. Finally, you need to change the Debug.Print of the tdf name to the comparision, and add the flag to tell yourself that the tabls does(not) exist. The remainder is taken DIRECTLY from Ms. Access help!

This example creates a new TableDef object and appends it to the TableDefs collection of the Northwind Database object. It then enumerates the TableDefs collection and the Properties collection of the new TableDef.

Sub TableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim tdfLoop As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot

' Create new TableDef object, append Field objects
' to its Fields collection, and append TableDef
' object to the TableDefs collection of the
' Database object.
Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef&quot
tdfNew.Fields.Append tdfNew.CreateField("Date", dbDate)

dbsNorthwind.TableDefs.Append tdfNew

With dbsNorthwind
Debug.Print .TableDefs.Count & _
" TableDefs in " & .Name

' Enumerate TableDefs collection.
For Each tdfLoop In .TableDefs
Debug.Print " " & tdfLoop.Name
Next tdfLoop

With tdfNew
Debug.Print "Properties of " & .Name

' Enumerate Properties collection of new
' TableDef object, only printing properties
' with non-empty values.

For Each prpLoop In .Properties
Debug.Print " " & prpLoop.Name & " - " & _
IIf(prpLoop = "", "[empty]", prpLoop)
Next prpLoop

End With

' Delete new TableDef since this is a
' demonstration.
.TableDefs.Delete tdfNew.Name
.Close
End With

End Sub

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over