One of the more powerful features of DAO is its ability to change the structure of a database. During the development of your application, you may find that you could organize your information better by adding temporary tables to the database or adding fields to a table. You can add tables while working in an application by appending a TableDef object to the TableDefs collection; you can add fields to a table by appending a Field object to the Fields collection and then appending the Fields collection to the TableDefs collection. You can also change relationships or indexes between tables.
Before you begin modifying the objects in a database, it's helpful to get a summary of the object names and structures in the database. Getting this summary is called "mapping the database." The following example maps the Nwind.mdb database by looping through the elements in the TableDefs collection and transferring the structure to Sheet1 in the active workbook. This process is similar to scanning the headings of a worksheet in Microsoft Excel.
Dim db as Database
Dim i As Integer, j As Integer, col As Integer, rw as Integer
Dim system_Prefix as String, Current_TableName as String
Dim hidden_prefix as String
Set db = OpenDatabase("d:\access\sampapps\nwind.mdb")
With Worksheets("Sheet1")
col = 1
For i = 0 To db.TableDefs.Count - 1
Current_TableName = db.TableDefs(i).Name
' Omit jet system tables from the list
system_Prefix = Left(Current_TableName, 4)
hidden_prefix = Left(Current_TableName, 1)
If system_Prefix <> "MSys" And system_Prefix <> "USys" And
hidden_prefix <> "~" Then
.Cells(1, col) = db.TableDefs(i).Name
'Display the fields inside each table
rw = 2
For j = 0 To db.TableDefs(i).Fields.Count - 1
.Cells(rw, col) = db.TableDefs(i).Fields(j).Name
.Cells(rw, col + 1) = db.TableDefs(i).Fields(j).Type
rw = rw + 1
Next j
col = col + 2
End If
Next i
End With
You can set up a similar procedure to loop through the query names in the database by specifying the QueryDefs collection instead of the TableDefs collection and adjusting the index and looping variables.
You can use DAO to change the structure of your database programatically. Modifying the database is similar to creating it initially. In most cases, you use the same Create and Append methods to add objects. You can add new TableDef objects to a database or add new Field and Indexes objects to existing tables. In addition, you can delete a TableDef object from a database or delete an Index object from a TableDef object.
There are some restrictions that apply to deleting field objects, however. For example, you cannot change an individual field after it's been appended to a TableDef object. You can delete a Field object only if it's not part of any Index or Relation objects. To delete an individual Field object, use the Delete method of the TableDef object. To change a Field object, you must first add a new TableDef object that reflects the desired changes in the structure, and then you must move the data to the new table and delete the old table. For more information about modifying the database structure, see "TableDef object" in Help.
Note
To delete an indexed field, you must first delete the index. You must also delete any affected Relation objects before you can delete a Field object or a TableDef object that's part of a relationship.
In addition to defining your own tables, you can attach tables from any supported database to the database you're working in. When you attach an external table, the connection information is stored in your database, and the connection to the outside table is activated when you open the table. The data itself remains in the external database.
You can use attached tables the same way you use any other table in your database. You can create Recordset objects that include fields from attached and local tables, giving you the flexibility to perform queries with multiple databases. When you attach an external table, you must specify a SourceTableName string and a Connect string as you create the TableDef object.
Note
Because an attached table isn't controlled by Jet, you cannot change the fields inside the table, add new fields to its structure, or delete its indexes.
Use the CreateTableDef method to create a new table object in your database. The name of the table in the argument is the name of the table as it will appear in your database; the table may have a different name in its native database.
Dim tb as TableDef
Set tb = db.CreateTableDef("Attached Customers Table")
Set the SourceTableName property to the name of the table in the source database that you want to connect to. You must specify the table name as it appears in its native database.
tb.SourceTableName = "Customers"
Set the Connect property by first specifying the path and filename of the source database and then appending the new table to the Tabledefs collection.
tb.Connect = ";database=e:\access\sampapps\nwind.mdb" db.TableDefs.Append tb