لطفا منتظر باشید ...
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.CAUTIONWhen 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.NOTEIf 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.NOTEIt 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.TIPYou can create a temporary query definition by using a zero-length string for the name argument of the CreateQueryDef method.
