Class TableDefs (DAO VBA)

A TableDefs collection contains all stored TableDef objects in a database (Microsoft Access workspaces only). To use a TableDefs class variable it first needs to be instantiated, for example

Dim tbls as TableDefs
Set tbls = Workspaces(1).Databases(1).TableDefs

Append

Adds a new TableDef to the TableDefs collection.

The appended object becomes a persistent object, stored on disk, until you delete it by using the Delete method. The addition of a new object occurs immediately, but you should use the Refresh method on any other collections that may be affected by changes to the database structure. If the object you're appending isn't complete (such as when you haven't appended any Field objects to a Fields collection of an Index object before it's appended to an Indexes collection) or if the properties set in one or more subordinate objects are incorrect, using the Append method causes an error. For example, if you haven’t specified a field type and then try to append the Field object to the Fields collection in a TableDef object, using the Append method triggers a run-time error.

Append (Object)

Object: An object variable that represents the field being appended to the collection.

Dim tbls As DAO.TableDefs: Set tbls = 
tbls.Append Object:=

Count

Returns the number of objects in the specified collection.

Because members of a collection begin with 0, you should always code loops starting with the 0 member and ending with the value of the Count property minus 1. If you want to loop through the members of a collection without checking the Count property, you can use a For Each...Next command. The Count property setting is never Null. If its value is 0, there are no objects in the collection.

Dim tbls As DAO.TableDefs: Set tbls = 
tbls.Count

Delete

Deletes the specified TableDef object from the TableDefs collection.

The Delete method is supported only when the TableDef object is new and hasn’t been appended to the database, or when the Updatable property of the TableDef is set to True.

Delete (Name)

Name: The name of the TableDef to delete.

Dim strName As String: strName = 
Dim tbls As DAO.TableDefs: Set tbls = 
tbls.Delete Name:=strName

Item

Item (Item)

Refresh

Updates the objects in the specified colletion to reflect the database's current schema.

To determine the position that the Microsoft Access database engine uses for Field objects in the Fields collection of a QueryDef, Recordset, or TableDef object, use the OrdinalPosition property of each Field object. Changing the OrdinalPosition property of a Field object may not change the order of the Field objects in the collection until you use the Refresh method Use the Refresh method in multiuser environments in which other users may change the database. You may also need to use it on any collections that are indirectly affected by changes to the database. For example, if you change a Users collection, you may need to refresh a Groups collection before using the Groups collection. A collection is filled with objects the first time it's referred to and won't automatically reflect subsequent changes other users make. If it's likely that another user has changed a collection, use the Refresh method on the collection immediately before carrying out any task in your application that assumes the presence or absence of a particular object in the collection. This will ensure that the collection is as up-to-date as possible. On the other hand, using Refresh can unnecessarily slow performance.

Dim tbls As DAO.TableDefs: Set tbls = 
tbls.Refresh