CREATING A DATABASE WITH DAO
To help demonstrate the different objects that make up your database, you're going to learn how to create a copy of your database by using nothing but DAO. You'll copy tables, queries, and relationships property by property. You'll also copy each record in every table field by field.
Note
This example doesn't provide the most efficient method for copying a database. It does, however, demonstrate how to access and use each object in DAO.
While you move through the examples, you're told where you can go in the Access user interface to view how Access displays the information available in DAO. This should help give you a clearer picture of how DAO is organized.
You can find the Chap05.mdb database on this book's CD-ROM in the ExamplesChap05 folder. This database consists of a single form and a single code module. All the code for managing the copying of the database is contained in the code module modCopyDatabase. In this code module, you find the subroutines shown in Table 5.1. (These subroutines are discussed in more detail in the following sections.)
The code in Chap05.mdb doesn't copy forms, code modules, or reports. Although DAO provides a method for enumerating these pieces as document objects, there's no mechanism through DAO to create new document objects. Nor can you copy individual documents through DAO to another database. But you can copy these objects from the currently active database to another database by using Access's CopyObject method.
The Database Copy Utility form, frmDatabaseCopy, is a simple dialog that lets you select a source and destination database to copy (see Figure 5.2). You can also specify on this form whether to copy just the structures of the tables or both the structure and the data. To get the filenames, this form uses the API call to the File Open common dialog for requesting the file to copy and the new file to copy to. More information about using API calls can be found in Chapter 15, “Using API Calls to Extend the Power of Access.”
Figure 5.2. Make a copy of your database by using the Database Copy Utility form.
Note
Demonstrating that DAO is a true component, all code in the code module can also be written and run in any Microsoft application that supports VBA and DAO.
Creating the Database Object
Listing 5.4 shows the CopyDatabase subroutine. This subroutine takes a path to the source database, the destination database, and a Boolean value that indicates whether only the structures should be copied, or the structures and the data should be copied.
Listing 5.4. Chap05.mdb: Copying a Database with DAO
Creating or opening databases with DAO is very easy with the CreateDatabase and OpenDatabase methods. Both methods must be called on a Workspace object.
Opening Existing Databases
The OpenDatabase method lets you specify how to open the database. In Listing 5.4, just the database name is supplied. You can also optionally supply information to open the database, as shown in the following table. The syntax for the OpenDatabase method is as follows:
Set database = workspace.OpenDatabase(dbname, exclusive, _ read–only, source)
Creating New Databases
Creating a database requires a path and filename to store the new database and the database's locality. The locality defines how your database sorts data and locates matches. Most often, the locality you use is dbLangGeneral, which specifies the ordering used by English, German, French, Portuguese, Italian, and Spanish. If your database is being used in other locales, you can open the database so that it sorts for their locality. For example, in Russia you may want sorting to occur differently.
You also can choose to have the newly created database encrypted. Omitting the options argument creates an unencrypted database. The syntax for the CreateDatabase method is
Set database = workspace.CreateDatabase (databasename, locale, options)
Compacting Existing Databases
To create an encrypted database or to switch the locale of the database, you need to create a new copy from the existing database. Although you can easily modify the sample code in Listing 5.4 to do this, Access 2000 does provide the CompactDatabase method for doing this quickly.
The compact database is a method of the DBEngine object. The syntax for the CompactDatabase method is
DBEngine.CompactDatabase olddb, newdb, Locale , options, Password
At a minimum, you must supply the existing database name (olddb) and a new database name (newdb). The new database name must be different from the existing one. By using the Dstlocale, SrcLocale, options, and Password parameters, you can specify new localities, encryption, and password.
This method doesn't return the newly created database. To use the new database after calling the CompactDatabase method, you must open the database with the OpenDatabase method.
Note
To encrypt a database from within Access, you must first close all open databases. From the Tools menu, you can now choose Database Utilities to compact, repair, and convert databases from previous Access versions, or choose Security to encrypt and set up user accounts. For more information about encrypting a database, see Chapter 21, “Securing Your Application.” You can also convert to previous Access versions (namely 97) in Access 2000, and as of Access 97, you can compact the open database in place.
The modGlobalUtilities module of the book's sample application (VideoApp.mdb) provides code to demonstrate how you can request your users in a multiuser system to log off, as well as automatically try to compact the back-end database. This database can be found in the Examples folder on the CD-ROM accompanying this book. This code using DAO is discussed in Chapter 26, “Startup Checking System Routines Using DAO.”
Repairing the Database Through DAO
Occasionally, your database can become corrupted. When this happens, DAO exposes a way for you to repair the database through the Tools menu. As with most actions in Access, you can also repair your database in code through DAO with the RepairDatabase method. RepairDatabase is on the DBEngine object and takes a single argument, the path to the database.
The database you're repairing must be opened exclusively, meaning that you can't repair an open database. Also, in a multiuser environment, no other users can have the database open, even with shared access.
The modGlobalUtilities module of the VideoApp.mdb application provides code to demonstrate how you can request your users in a multiuser system to log off, as well as automatically try to repair the back-end database. You can use this code to perform administrative tasks to the back end, such as repair the database. This database can be found in the Examples folder on the CD-ROM accompanying this book. This code is discussed in Chapter 26.
Copying Table Structures
By examining the DAO object hierarchy, notice that tables consist of fields, indexes, and properties. Thus, with each table, the fields, indexes, and properties collections must also be copied. The TableDef object represents all the characteristics available when designing a table in the table designer.
The window in Figure 5.3 shows the derivation of most of the information in the TableDef object. (I say “most” because the descriptions aren't copied.) The Fields collection corresponds to the list of fields in the table. Properties about each field are displayed at the bottom of the window, on the General page.
Figure 5.3. Creating and editing tables in Access actually sets the properties of the TableDef object.
To view most of the properties of a TableDef object, choose Properties from the View menu.
To view the Indexes collection of the TableDef object, from the View menu choose Indexes. Notice that each index can have a list of fields. These fields are contained in a Fields collection of the Index object.
Listing 5.5 is a good example of when to use code to ignore system tables. You don't want to copy the system tables because they are created and managed automatically by the database engine as you copy information. Actually, trying to create a table with the name of an existing system table generates errors.
Listing 5.5. Chap05.mdb: Copying the TableDefs Collection
In Listing 5.5, the objDest argument is being passed as a generic object so that the CopyIndexes subroutine can be reused to copy indexes of the TableDef and Relations objects.
The generic CopyProperties subroutine (see Listing 5.6) copies all the properties for the object. DAO exposes a Properties collection on almost all its objects. To cover cases where a property may not have a value, a simple error handler is included in the code.
Listing 5.6. Chap05.mdb: Copying All Properties for the Object
Sub CopyProperties(objSrc As Object, objDest As Object)
Dim prpProp As Property, temp As Variant
On Error GoTo errCopyProperties
For Each prpProp In objSrc.Properties
objDest.Properties(prpProp.Name) = prpProp.Value
Next
On Error GoTo 0
Exit Sub
errCopyProperties:
Resume Next
End Sub
|
Notice that creating a TableDef object in code consists of the following steps:
1. |
The CopyTables subroutine creates a blank TableDef object by declaring a new instance of the object for each table in the source database. Creating a TableDef object doesn't automatically append it to the database's TableDefs collection. Until the TableDef object is appended to the database, you can modify most of the properties. When appended, some properties, such as the TableDef object's Attributes property, become read-only. |
2. |
By using the CopyFields subroutine (this subroutine's code appears later in the section “Fielding the Field Object”), the fields are copied from the source TableDef and appended to the newly created TableDef. (The Field object works similarly to the TableDef object.) |
3. |
After the fields are appended, indexes based on those fields can be created. Indexes are composed of their own sets of fields. The fields in the index must be existing fields from the table. For this reason, the fields are appended to the TableDef before the indexes are. Again, the index must be created and then appended to the TableDef. For most objects in DAO, you need to create the object and then append it to the appropriate collection. However, this isn't always true, as you'll learn in the next section. |
4. |
These steps explain the simple process of creating a TableDef. However, they don't point out the details of the TableDef object. When creating a TableDef, you need to answer the following questions:
What do you want to name the table?
Is the table an attached table? If so, what are the attributes relating to this attachment, what's the connect string, and what's the source table's name?
You can answer these questions immediately when you create the TableDef object. By examining the following line of code, you can see that four properties are being set:
Set tbfDest = dbsDest.CreateTableDef(tbfSrc.Name, tbfSrc.Attributes, _
tbfSrc.SourceTableName, tbfSrc.Connect)
The four properties being set are as follows:
Attributes The various attributes that can be set for a table. For an examination of the Attributes property, see the earlier section “Examining Table Attributes.”
You can set these four properties when creating the table or at any time up to appending the table to the TableDefs collection. After a table is appended, you can still change the name and connect string, but you can't modify the attributes or the source table name.
Fielding the Field Object
The most popular of all DAO objects is Field. It exists in TableDefs, indexes, and relations. The code in Listing 5.7 is the CopyFields subroutine that's called by the CopyTableDefs subroutine. The CopyFields subroutine copies fields from any source object with a Fields collection to any destination object.
Listing 5.7. Chap05.mdb: Copying the Fields Collection
Sub CopyFields(objSrc As Object, objDest As Object)
Dim fldSrc As Field, fldDest As Field
For Each fldSrc In objSrc.Fields
If TypeName(objDest) = "TableDef" Then
Set fldDest = objDest.CreateField(fldSrc.Name, _
fldSrc.Type, fldSrc.Size)
Else
Set fldDest = objDest.CreateField(fldSrc.Name)
End If
CopyProperties fldSrc, fldDest
objDest.Fields.Append fldDest
Next
Exit Sub
End Sub
|
In the arguments for the CopyFields subroutine, notice that the source and destination tables are passed as objects rather than as TableDefs. This is a good example of where you can use a generic object type to write reusable code. The same CopyFields subroutine is used to copy fields between indexes and relations.
Also, how the field is created depends in the object. Based on the object, different properties are available in the field. For example, a field in a table must have a specific size and type. In a relationship and index, however, all that's necessary is the field name, because on those objects the field name refers back to the TableDef.
Note
One disadvantage to using the CopyFields routine and generic object types is that VBA fails to do any type checking on the calling parameters. Any pair of objects can be passed into this routine without causing a compile-time error. The only indication of improperly calling this routine would be a runtime error. No compile-time error would occur. You can use TypeOf() to determine what type of field you are dealing with, thus making the code more robust in critical places.
Copying Queries
The QueryDef object stores all your query definitions. This object exposes SQL queries in an object-oriented approach. You can access the list of fields that make up a query, as well as the parameters in parameterized queries. These collections are created from the SQL statement. Therefore, when you create a query through DAO, you supply only the appropriate SQL statement; DAO does the rest. You can't append to the parameters or the Fields collection on a QueryDef object.
Creating queries is the exception to the pattern you've been seeing. Creating a QueryDef automatically appends the query to the database. You don't call the Append method on the QueryDef object.
The CopyQueries subroutine (see Listing 5.8) shows how to copy a query from a source database to a destination database. Different from all the other copy subroutines provided earlier, the query is created directly in the database and isn't appended to the database.
Listing 5.8. Chap05.mdb: Copying the QueryDefs Collection
Creating Temporary Queries
You can use the CreateQueryDef method to create temporary queries. To create a temporary query, you call the CreateQueryDef method but supply an empty string as the query's name.
If after creating this query you want to append it to the database, you must provide the query with a name and then append the query to the QueryDefs collection. Trying to append a query that doesn't have a name generates a runtime error.
Tip
Because creating temporary queries tends to “bloat” the database, you will want to take advantage of a new feature in Access 2000 that allows you to compact the current database on exiting the application. To set this feature, choose Options from the Tools menu. On the General page, check the Compact on Close option. (The compacting will occur only if the database size will be reduced by 256KB.)
Compiling Queries
Queries that are executed and opened from QueryDef objects run more quickly than executing SQL statements. For example, to create a query that returns all the customers in a specific state, write the following function that creates the SQL statement and executes it:
Function StateQuery(strState as String) as Recordset
Dim dbsCurrent as Database
Set dbsCurrent = Currentdb()
' Chr(34) is used to insert a quotation mark.
Set StateQuery = dbsCurrent.OpenRecordset("Select * From [Customers]" _
& "Where [State] = " & chr(34) & strState & chr(34))
End Function
A more efficient way to execute this query is to create it as a parameterized query through the query designer and save the query. The database engine can then pre-optimize saved queries. You can view the query for this example in the Chap05.mdb sample application, which you can find in the ExamplesChap05 folder on the accompanying CD-ROM. The query name is qryCustomersInState. Rewriting the preceding StateQuery() function to use the compiled query results in the following:
Function StateQuery(strState as String) as Recordset
Dim qdState as QueryDef
Dim dbsCurrent as Database
Set dbsCurrent = Currentdb()
Set qdState = dbsCurrent.QueryDefs("qryCustomersInState")
qdState.Parameters("WhatState") = strState
Set StateQuery = qdState.OpenRecordset
End Function
In the function, the QueryDef for the query is referenced. When you execute parameterized queries, you must first set the parameters through the QueryDef object. The parameters are stored in a Parameters collection.
Note
A parameterized query requires arguments to be run. If the arguments aren't supplied, you receive an error when the query is executed.
After you set the parameters, call the OpenRecordset method on the QueryDef object to return the recordset for the currently specified parameters. To execute queries that don't have a Parameters collection, you can just open the recordset from the database object. For example, to open a query called qMoviesbyCategory, you can use the following code:
Function MoviesByCategory() as Recordset
Dim dbsCurrent as Database
Set dbsCurrent= Currentdb()
Set MoviesByCategory = dbsCurrent.OpenRecordset("qMoviesbyCategory")
End Function
You don't need to go through the QueryDefs collection when running queries that aren't parameterized. It's much simpler just to use the OpenRecordset method on the current database object.
Copying Relationships
Relationships can exist between any two tables or queries in your database. A relation consists of a primary key and a foreign key. The primary key is one or more fields in a table that uniquely identify a record. The foreign key is one or more fields from another table that refer to the primary key.
For example, in a customer order-entry system, you can have one table containing all your customers and another table containing all the customer orders. In the Customers table, the CustomerID field uniquely identifies a customer. Therefore, in designing the tables, the Orders table would also have a CustomerID field. However, quite a few records for each customer are probably in the Orders table, because one record represents one order. In defining this relationship, the primary key is the CustomerID in the Customers table, and the foreign key is the CustomerID in the Orders table. Because each customer can have many orders, this type of relationship is called a one-to-many relationship.
All these characteristics are available on the Relation object. The Attributes property represents the joining relationship between the two tables or queries. The Attributes property is used to distinguish all the information about the relationship available in the Define Relationship dialog box. To display the relationships available on the current database, from the Tools menu choose Relationships. This view corresponds to the Relationships collection in DAO. Figure 5.4 shows an example of this.
Figure 5.4. This window shows the relationship layout for the sample application Northwind.mdb, which comes with Access.
To view a specific relationship object, you can double-click any lines connecting two tables together. This brings up a dialog box similar to the one in Figure 5.5.
Figure 5.5. In this dialog, you can define the type of relationship and whether referential integrity should be enforced.
Defining a relationship occurs through the window in Figure 5.4 and the dialog in Figure 5.5. The Table/Query column in the Relationships dialog represents the primary key in the relationship, whereas the Related Table/Query column represents the foreign key. The fields selected in these lists are represented by the Fields collection of the relationship object. In this collection, you can get each field that's taking part in the relationship, the primary key, and the foreign key.
The referential integrity and the join information are available on each individual relation object. Referential integrity is used to ensure that each and every foreign key has a primary key. Listing 5.9 shows how to copy the relationships.
Listing 5.9. Chap05.mdb: Copying the Relations Collection
Copying Data
Up to this point, you've created a copy of your database's table, query, and relationship definitions. Now, your new database consists of no data. In this section, you perform the data transfer by using just DAO calls. Opening tables in DAO is done by using the OpenRecordset method on the database object (see Listing 5.10).
Listing 5.10. Chap05.mdb: Copying Data by Using Workspaces
When the recordsets are opened, extra parameters are specified. The source table is specified to be opened as a read-only table. These customizations give you more flexibility and can be used to increase performance. Opening for read-only increases performance because the database engine knows you'll never be making any modifications.
You can use many other options to open recordsets. You can specify in a multiuser environment that no one else can modify or add records (dbDenyWrite), that other users can't even view the records (dbReadOnly), and whether in a multiuser environment you get a runtime error if you try to edit data that another user is editing (dbSeeChanges). Many of these options can be combined to give you further control. Combining options is done with the And operator. To make a recordset that denies reads and writes to other users, you specify the options as dbDenyWrite And dbDenyRead.