Creating Tables and Fields

For our invoicing system, we have two tables to create. The basic procedure for creating a table in code is as follows:

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

2. Create the table object using the Database's CreateTableDef method.

3. Create the Field objects in memory, using the TableDef's CreateField method, setting each field's attributes as appropriate.

4. Append each Field object to the TableDef's Fields collection.

5. Append the TableDef object to the Database's TableDefs collection.

6. Refresh the TableDefs 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 dbs As Database Dim tdf As DAO.TableDef Dim fldInvNo As DAO.Field Dim fldInvDate As DAO.Field Dim fldCustID As DAO.Field Dim fldComments As DAO.Field Set dbs = CurrentDb On Error Resume Next

'If the table already exists, delete it dbs.TableDefs.Delete "tblInvoice" On Error GoTo 0

'Create the table definition in memory Set tdf = dbs.CreateTableDef("tblInvoice")

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

'Create the field definitions in memory

Set fldInvNo = tdf.CreateField("InvoiceNo", dbText, 10) fldInvNo.AllowZeroLength = False fldInvNo.Required = True

'The InvoiceNo field could also have been specified thus: 'Set fldInvNo = tdf.CreateField() 'With fldInvNo ' .Name = "InvoiceNo"

' .AllowZeroLength = False

'End With

Set fldInvDate = tdf.CreateField(MInvoiceDateM, dbDate) fldInvDate.Required = True

Set fldCustID = tdf.CreateField("CustomerID", dbLong) fldCustID.Required = True

Set fldComments = tdf.CreateField(MCommentsM, dbText, 50) fldComments.AllowZeroLength = True fldComments.Required = False

'Append the fields to the TableDef's Fields collection tdf.Fields.Append fldInvNo tdf.Fields.Append fldInvDate tdf.Fields.Append fldCustID tdf.Fields.Append fldComments

The fields have now been added to the table, but the table still needs to be added to the TableDefs collection to make it a permanent fixture. Once you've done that, you should refresh the TableDefs 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 TableDef to the Database's TableDefs collection

dbs.

.TableDefs.Append tdf

'Refresh the TableDefs collection

dbs.

TableDefs.Refresh

Set

fldInvNo = Nothing

Set

fldInvDate = Nothing

Set

fldCustID = Nothing

Set

fldComments = Nothing

Set

tdf = Nothing

Set

dbs = Nothing

End Sub

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 price. 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 additional attribute definition, dbAutoIncrField, to create an AutoNumber field.

Public Sub CreateInvItemTable() Dim dbs As Database Dim tdf As DAO.TableDef Dim fldInvItemID As DAO.Field Dim fldInvNo As DAO.Field Dim fldProductID As DAO.Field Dim fldQty As DAO.Field Dim fldUnitPrice As DAO.Field

Set dbs = CurrentDb On Error Resume Next

'If the table already exists, delete it If IsObject(dbs.TableDefs("tblInvItem")) Then dbs.TableDefs.Delete "tblInvItem" End If

'Create the table definition in memory Set tdf = dbs.CreateTableDef("tblInvItem")

'Create the field definitions in memory

Set fldInvItemID = tdf.CreateField("InvItemID", dbLong) 'Make the field an AutoNumber datatype fldInvItemID.Attributes = dbAutoIncrField fldInvItemID.Required = True

Set fldInvNo = tdf.CreateField("InvoiceNo", dbText, 10) fldInvNo.Required = True fldInvNo.AllowZeroLength = False

Set fldProductID = tdf.CreateField("ProductID", dbLong) fldProductID.Required = True

Set fldQty = tdf.CreateField("Qty", dbInteger) fldQty.Required = True

Set fldUnitPrice = tdf.CreateField("UnitCost", dbCurrency) fldUnitPrice.Required = False

'Append the fields to the TableDef's Fields collection tdf.Fields.Append fldInvItemID

tdf.Fields.Append fldInvNo tdf.Fields.Append fldProductID

tdf.Fields.Append fldQty tdf.Fields.Append fldUnitPrice

'Append the TableDef to the Database's TableDefs collection dbs.TableDefs.Append tdf

'Refresh the TableDefs collection dbs.TableDefs.Refresh

Set fldInvItemID = Nothing

Set fldInvNo = Nothing

Set fldProductID = Nothing

Set fldQty = Nothing

Set fldUnitPrice = Nothing

Set tdf = Nothing

Set dbs = Nothing End Sub

0 0

Post a comment