Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

DAO: Adding Fields To A TableDef Object

How do you add fields to a DAO TableDef object? You can use that object’s CreateField method to do that, passing that method the name of the new field and a constant indicating that field’s type:

TableDef.CreateField( FieldName, FieldType)

Here are the constants specifying the possible field types:

•  dbBigInt

•  dbBinary

•  dbBoolean

•  dbByte

•  dbChar

•  dbCurrency

•  dbDate

•  dbDecimal

•  dbDouble

•  dbFloat

•  dbGUID

•  dbInteger

•  dbLong

•  dbLongBinary (OLE object)

•  dbMemo

•  dbNumeric

•  dbSingle

•  dbText

•  dbTime

•  dbTimeStamp

•  dbVarBinary

Let’s see an example to make this clearer. In the previous topic, we created a TableDef object named td for the daocode example project (see the first topic in this chapter), and now we can add two fields to that object, which we declare in an array named fields of type Field (which is defined in the DAO library):

Dim fields(2) As Field

The users have specified what names they want to give to those two new fields in the New Table dialog box’s text boxes, so we create the new fields this way:

Sub CreateTable()
    Set td = db.CreateTableDef(TableForm.Text1.Text)

    Set fields(0) = td.CreateField(TableForm.Text2.Text, dbText)
    Set fields(1) = td.CreateField(TableForm.Text3.Text, dbText)
...

Now that the new fields are created, we can append them to the actual TableDef object td:

Sub CreateTable()
    Set td = db.CreateTableDef(TableForm.Text1.Text)

    Set fields(0) = td.CreateField(TableForm.Text2.Text, dbText)
    Set fields(1) = td.CreateField(TableForm.Text3.Text, dbText)
    td.fields.Append fields(0)
    td.fields.Append fields(1)
...
End Sub

That’s it—we’ve defined two new fields, named them, and appended them to a TableDef object. Next, we’ll add an index to our table to allow the user to sort the data in that object.

DAO: Adding An Index To A TableDef Object

You use an index to sort a table, and you create an index with the DAO CreateIndex method. The CreateIndex method creates an Index object, and you can make one of the fields in a table that table’s index with that Index object’s CreateField method.

Let’s see an example to make this clearer. We’ll create an index for our DAO example, the daocode project (see the first topic in this chapter) named dbindex, which we declare as a form-wide variable:

Dim dbindex As Index

We name the index when we create it; here, we’ll just use the first field that the user has placed in this table as the table’s index so all sort operations will sort using that field. In this example, we name our index by adding the word “index” to the name of that field this way:

Sub CreateTable()
    Set td = db.CreateTableDef(TableForm.Text1.Text)

    Set fields(0) = td.CreateField(TableForm.Text2.Text, dbText)
    Set fields(1) = td.CreateField(TableForm.Text3.Text, dbText)
    td.fields.Append fields(0)
    td.fields.Append fields(1)

    Set dbindex = td.CreateIndex(TableForm.Text2.Text & "index")
...

Next, we create a new field, indexfield, in the index, using the name of the first field in the table:

Sub CreateTable()
    Set td = db.CreateTableDef(TableForm.Text1.Text)

    Set fields(0) = td.CreateField(TableForm.Text2.Text, dbText)
    Set fields(1) = td.CreateField(TableForm.Text3.Text, dbText)
    td.fields.Append fields(0)
    td.fields.Append fields(1)

    Set dbindex = td.CreateIndex(TableForm.Text2.Text & "index")
    Set indexfield = dbindex.CreateField(TableForm.Text2.Text)
...

Finally, we append indexfield to our Index object, dbindex, and append that object to the TableDef object’s Indexes collection:

Sub CreateTable()
    Set td = db.CreateTableDef(TableForm.Text1.Text)

    Set fields(0) = td.CreateField(TableForm.Text2.Text, dbText)
    Set fields(1) = td.CreateField(TableForm.Text3.Text, dbText)
    td.fields.Append fields(0)
    td.fields.Append fields(1)

    Set dbindex = td.CreateIndex(TableForm.Text2.Text & "index")
    Set indexfield = dbindex.CreateField(TableForm.Text2.Text)
    dbindex.fields.Append indexfield
    td.Indexes.Append dbindex
...
End Sub

And that’s it—we’ve created a new index for our table. In fact, we’ve set up the whole TableDef object td now, so we can create a record set to start working with data, and we’ll do that in the next topic.