Example 15-1 creates a new database named MoreBks.mdb on the directory c:/temp and then lists the tables that are contained in the database.

Sub exaCreateDb(  )

Dim dbNew As DATABASE
Dim tbl As TableDef

Set dbNew = CreateDatabase _
("c:	empMoreBks", dbLangGeneral)

For Each tbl In dbNew.TableDefs
    Debug.Print tbl.Name
Next

dbNew.Close

End 
                     Sub

The program in Example 15-1 displays the following list of tables:

These tables are created by Microsoft Access for its own use.

Fields are created for a table using the CreateField method of the TableDef object. The syntax is:

SetFieldVar =
TableDefVar.CreateField _
([FieldName[, type [, size]]])

where:

Note

To remove a field from a TableDef object, use the Delete method.

Field objects have a variety of properties, among which are:

AllowZeroLength

True if a zero-length value is valid for a text or memo field. (Setting this property for a nontext field generates an error.)

DefaultValue

Sets or returns the default value of a Field object.

Required

True indicates that a null value is not allowed.

ValidationRule and ValidationText

Used for validation of field values. (See the following example.)

The procedure in Example 15-2 creates a new table named NewTable, creates a new field named NewField, sets certain properties of the field and appends it to the Fields collection, and then appends the new table to the TableDefs collection.

Example 15-2. A CreateTableDef method example

Sub exaCreateTable(  )

Dim db As DATABASE
Dim tblNew As TableDef
Dim fld As Field

Set db = CurrentDb

Set tblNew = db.CreateTableDef("NewTable")
Set fld = tblNew.CreateField("NewField", dbText, 100)

' Set properties of field BEFORE appending

' zero length value is OK
fld.AllowZeroLength = True
' default value is 'Unknown'
fld.DefaultValue = "Unknown"
' Null value not allowed
fld.Required = True
' Validation
fld.ValidationRule = "Like 'A*' or Like 'Unknown'"
fld.ValidationText = "Known value must begin with A"

' Append field to Fields collection
tblNew.Fields.Append fld

' Append table to TableDef collection
db.TableDefs.Append tblNew

End 
                        Sub

Setting the validation properties of a field requires setting two properties. The ValidationRule property is a text string that describes the rule for validation, and the ValidationText is a string that is displayed to the user when validation fails. After running the code from Example 15-2, a new table appears in the Access Database window. (You may need to move away from the Tables tab and then return to that tab to see the new table.) Opening this table in Design View shows the window in Figure 15-1. Note that the Field Properties setting reflects the properties set in our code.

Figure 15-1. Design view of table generated from running exaCreateTable

Incidentally, TableDef objects also have ValidationRule and ValidationText properties, used to set validation rules that involve multiple fields in the table.

I have remarked that some properties that are read/write before the object is appended to its collection become read-only after appending. One such example is the Type property of a field. On the other hand, the Name property of a field can be changed. This is an example of a change that can be made using DAO but not by using SQL.

Example 15-4 illustrates the use of Relation objects. In this example, we will create a new relation in the LIBRARY database. The first step is to create a new table, using Microsoft Access. Call the table SALESREGIONS, and add two text fields: PubID and SalesRegions. Then add a few rows shown in Table 15-3 to the table.

The code in Example 15-4 creates a relation between the PubID field of the PUBLISHERS table (the primary key) and the PubID field of the SALESREGIONS table (the foreign key).

Sub exaRelations(  )

Dim db As DATABASE
Dim rel As Relation
Dim fld As Field

Set db = CurrentDb

' Create relation
Set rel = db.CreateRelation("PublisherRegions", _
"PUBLISHERS", "SALESREGIONS")

' Set referential integrity with cascading updates
rel.Attributes = dbRelationUpdateCascade

' Specify the key field in referenced table
Set fld = rel.CreateField("PubID")

' Specify foreign key field in referencing table.
fld.ForeignName = "PubID"

'Append Field object to Fields collection of 
' Relation object.
rel.Fields.Append fld
    
' Append Relation object to Relations collection.
db.Relations.Append rel

End Sub

After running this code, make sure the Database window is active, and select Tools → Relationships from the Access menu bar. Then select Relationships → Show All, and you should see a window similar to that in Figure 15-3, showing the new relationship.

Figure 15-3. Relationships window after running exaRelations

Recall from Chapter 6 that Microsoft Access supports several types of queries. In particular, a select query returns a recordset. An action query does not return a recordset, but rather takes action on existing data, such as making a new table, deleting rows from a table, appending rows to a table, or updating the values in a table.

If a QueryDef object represents an action query, then we can use its Execute statement to run the query. If the QueryDef object represents a select query, then we can open the corresponding result table (recordset) using the OpenRecordset method on the QueryDef object. Let us illustrate. The code in Example 15-5 creates a new select query and displays the record count for the resulting recordset.

Sub exaCreateSelect(  )

Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String
Dim rs As Recordset

Set db = CurrentDb

' Create an SQL SELECT statement
strSQL = "SELECT * FROM BOOKS WHERE Price > 20"

' Create a new QueryDef object
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

' Open a recordset for this query
Set rs = qdf.OpenRecordset

' Move to end of recordset
rs.MoveLast

' Show record count
MsgBox "There  are " & rs.RecordCount & " books with price exceeding $20"

End Sub

The code in Example 15-6 creates a new action query and executes it. The effect is to raise the price of each book in the BOOKS table by 10%.

Sub exaCreateAction(  )

' Creates an action query and executes it

Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb

' Create an SQL UPDATE statement
' to raise prices by 10%
strSQL = "UPDATE BOOKS SET Price = Price*1.1"

' Create a new QueryDef object
Set qdf = db.CreateQueryDef("PriceInc", strSQL)

qdf.Execute

End Sub

Note that once a QueryDef object exists, we may still use the OpenRecordset or Execute methods to run the query. The Execute method can also be used on a Database object to run an SQL statement. Here is an example that reduces the price of each book in the BOOKS table by 10%:

Dim db As DATABASE
Set db = CurrentDb
db.Execute "UPDATE BOOKS SET Price = Price*0.9"

When a QueryDef object is created or changed, Jet sets certain properties, such as DateCreated, LastUpdated, and Type. (Note that the QueryDefs collection may need refreshing before these properties can be read.) Some of the possible query types are listed in Table 15-4.

The RecordsAffected property returns the number of records affected by the last application of the Execute method. Let us illustrate.

Example 15-7 modifies the earlier action-query example to perform the action (10% price increase) if and only if the increase will affect more than 15 books in the table. This is done using the BeginTrans, Committrans, and Rollback properties of the current Workspace object.

Sub exaCreateAction2(  )

Dim ws As Workspace
Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String

Set ws = DBEngine(0)
Set db = CurrentDb

' Create an SQL UPDATE statement
' to raise prices by 10%
strSQL = "UPDATE BOOKS SET Price = Price*1.1

' Create a new QueryDef object
Set qdf = db.CreateQueryDef("PriceInc", strSQL)

' Begin a transaction
ws.BeginTrans

' Execute the query
qdf.Execute

' Check the number of records affected and either roll back transaction or proceed
If qdf.RecordsAffected  <= 15 Then
    MsgBox qdf.RecordsAffected & " records affected " & _
           "by this query. Transaction cancelled."
    ws.Rollback
Else
    MsgBox qdf.RecordsAffected & " records affected " & _
           "by this query. Transaction completed."
    ws.CommitTrans
End If

End Sub