Creating Tables and Columns

Let's replicate the design of the two invoicing system tables we created in Chapter 6. The basic procedure for creating a table in ADO is as follows:

1. Create a Catalog object and define its ActiveConnection property.

2. Create a Table object.

3. Check if the table already exists, and if so, delete it.

4. Create the table object in memory using the New keyword.

5. Create the Column objects in memory, using the table's Append method, setting each column's attributes as appropriate.

6. Append the Table object to the catalog object's Tables collection.

7. Refresh the Tables collection to ensure it is up-to-date.

The header table stores the basic high-level information about each invoice, such as the invoice number, date, and the customer ID. The following example demonstrates how to create a new table called tblInvoice and add four fields to it.

First, let's declare all the objects needed to create the table.

Public Sub CreateInvoiceTable() Dim cat As ADOX.Catalog Dim tbl As ADOX.Table

'Create and connect a Catalog object Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection On Error Resume Next

'If the table already exists, delete it cat.Tables.Delete "tblInvoice" On Error Goto 0

'Create the table definition in memory Set tbl = New ADOX.Table tbl.Name = "tblInvoice"

At this point, you have created a new table object, but it only exists in memory. It won't become a permanent part of the database until you add it to the catalog object's collection. Before you do that, however, you need to add one or more columns (called fields in DAO) to the table, because you can't save a table that has no columns.

'Create the new columns tbl.Columns.Append "InvoiceNo", adVarChar, 10

'The InvoiceNo column could also have been specified thus: 'Dim col As ADOX.Column 'Set col = New ADOX.Column 'With col

' .Name = "InvoiceNo" ' .Type = adVarChar

'tbl.Columns.Append col

'Create the remaining columns tbl.Columns.Append "InvoiceDate" adDBDate tbl.Columns.Append "CustomerID" adInteger tbl.Columns.Append "Comments" adVarChar, 50

The columns have now been added to the table, but the table still needs to be added to the catalog's Tables collection to make it a permanent fixture. Once you've done that, you should refresh the Tables collection to ensure it is up-to-date, because in a multiuser application, the new table may not be immediately propagated to other users' collections until you do.

'Append the new table to the collection cat.Tables.Append tbl cat.Tables.Refresh

'Clean up cat.ActiveConnection = Nothing Set tbl = Nothing Set cat = Nothing

Next, we need to create a table to store the invoice line items, including the product ID, the number of items sold, and their individual unit prices. Since the total invoice price and tax can be calculated at runtime, we won't violate normalization rules by creating fields for these items.

The following example creates a new table called tblInvItem, and adds five fields to it. It is based on the same basic procedure for creating tables, but includes an addition attribute definition, dbAutoIncrField, to create an AutoNumber field.

Public Sub CreateInvItemTable() Dim cat As ADOX.Catalog Dim tbl As ADOX.Table

'Create and connect the Catalog object Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection On Error Resume Next

'If the table already exists, delete it cat.Tables.Delete "tblInvItem" On Error Goto 0

'Create the table definition in memory Set tbl = New ADOX.Table tbl.Name = "tblInvoice"

With tbl.Columns

.Append "InvItemID", adInteger .Append "InvoiceNo", adVarChar, 10 .Append "ProductID", adInteger .Append "Qty", adSmallInt .Append "UnitCost", adCurrency End With

Once you've appended a column to the table, you can set its Access-specific properties. For example, to make a column (in this case, the InvItemID column) the AutoNumber column, you must first set its ParentCatalog property, and then set its AutoIncrement property.

With tbl.Columns("InvItemID") .ParentCatalog = cat .Properties("AutoIncrement") = True End With

'Append the new table to the collection cat.Tables.Append tbl cat.Tables.Refresh

'Clean up cat.ActiveConnection = Nothing Set tbl = Nothing Set cat = Nothing

0 0

Post a comment