Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Creating and Modifying Database Objects Using DAO Code

When developing an Access application, it might be useful to add tables or queries, define or modify relationships, change security, or perform other data-definition techniques at runtime. You can accomplish all this by manipulating the various Data Access Objects.

Adding a Table Using Code

Many properties and methods are available for adding and modifying Jet Engine objects. The code in Listing 14.70 creates a table, adds some fields, and then adds a primary key index.

Listing 14.70 Creating a Table, Adding Fields, and Adding a Primary Key Index

Sub CreateTable() Dim db As dao.Database Dim tbl As dao.TableDef Dim fld As dao.Field Dim idx As dao.Index 'Point the Database object at a reference to the 'current database Set db = CurrentDb() 'Point a TableDef object at a new TableDef Set tbl = db.CreateTableDef("tblFoods") 'Add fields to the TableDef object Set fld = tbl.CreateField("FoodID", dbLong, 5) tbl.Fields.Append fld Set fld = tbl.CreateField("Description", dbText, 25) tbl.Fields.Append fld Set fld = tbl.CreateField("Calories", dbInteger) tbl.Fields.Append fld db.TableDefs.Append tbl 'Add an index to the TableDef object Set idx = tbl.CreateIndex("PrimaryKey") 'Add a field to the Index object Set fld = idx.CreateField("FoodID") 'Set properties of the index idx.PRIMARY = True idx.Unique = True 'Add the field to the Fields collection of the Index object idx.Fields.Append fld 'Add the index to the Indexes collection of the Table object tbl.Indexes.Append idx End Sub

This code first creates a table definition called tblFoods. Before it can add the table definition to the TableDefs collection, it must add fields to the table. The code adds three fields to the table. Notice that the code specifies the field name, type, and length. After the code adds the table definition to the database, it adds indexes to the table. The index added in Listing 14.70 is a primary key index.

CAUTION

When running code that appends an object, an error occurs if the object already exists. You must either include error handling in your routine to handle this or delete the existing instance of the object before appending the new object.

Removing a Table Using Code

Just as you can add a table using code, you can remove a table using code, as shown in Listing 14.71.

Listing 14.71 Removing a Table

Sub DeleteTable() Dim db As dao.Database 'Point the Database object at a reference to the 'current database Set db = CurrentDb 'Use the Delete method of the TableDefs collection 'to delete a table called tblFoods db.TableDefs.Delete "tblFoods" End Sub

The Delete method is issued on the TableDefs collection. The table you want to delete is passed to the Delete method as an argument.

NOTE

If a relationship exists between the table that you are deleting and other tables in the database, an error occurs. You must therefore delete any relationships a table is involved in before deleting a table.

Establishing Relationships Using Code

When you create tables using the Access environment, you normally create relationships between the tables at the same time. If you are creating tables using code, you probably want to establish relationships between those tables using code as well. Listing 14.72 shows an example.

Listing 14.72 Establishing Relationships Between Database Objects

Sub CreateRelation() Dim db As dao.Database Dim rel As dao.Relation Dim fld As dao.Field 'Point the Database object at a reference to the 'current database Set db = CurrentDb 'Use the CreateRelation method of the Database object 'to create a Relation object Set rel = db.CreateRelation() 'Set properties of the Relation object With rel .Name = "PeopleFood" .Table = "tblFoods" .ForeignTable = "tblPeople" .Attributes = dbRelationDeleteCascade End With 'Set the primary key field of the Relation object Set fld = rel.CreateField("FoodID") 'Set the foreign key field of the Relation object fld.ForeignName = "FoodID" 'Add the Field object to the Fields collection of 'the Relation object rel.Fields.Append fld 'Append the Relation object to the Relations 'collection of the Database object db.Relations.Append rel End Sub

This code begins by creating a new Relation object. It then populates the Name, Table, Foreign Table, and Attributes properties of the relationship. After the properties of the relationship are set, the field is added to the Relation object. Finally, the Relation object is appended to the Relations collection.

Creating a Query Using Code

If you are running your application from the Access runtime, your users won't be able to design their own queries unless they have their own full copies of Access. You might want to build your own query designer into your application and then allow the users to save the queries they build. This requires that you build the queries yourself, after the user designs them. Listing 14.73 shows the code needed to build a query.

Listing 14.73 Building a Query

Sub CreateQuery() Dim db As dao.Database Dim qdf As dao.QueryDef Dim strSQL As String 'Point the Database object at a reference to the 'current database Set db = CurrentDb 'Create a QueryDef object called qryBigProjects Set qdf = db.CreateQueryDef("qryBigProjects") 'Designate the SQL associated with the QueryDef object strSQL = "Select ProjectID, ProjectName, ProjectTotalEstimate " _ & "From tblProjects " _ & "Where ProjectTotalEstimate >= 30000" qdf.SQL = strSQL End Sub

This code uses the CreateQueryDef method of the Database object to create a new query definition. It then sets the SQL statement associated with the query definition. This serves to build and store the query.

NOTE

It is important to understand that the CreateTableDef method does not immediately add the table definition to the database, unlike the CreateQueryDef method of the database object, which immediately adds the query definition to the database. You must use the Append method of the TableDefs collection to actually add the table definition to the database.

TIP

You can create a temporary query definition by using a zero-length string for the name argument of the CreateQueryDef method.