| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
Data access objects are used to create, modify, and remove Jet engine objects via code. They provide you with the flexibility of moving beyond the user interface to accomplish the manipulation of data and Jet engine objects. They can be used to perform the following tasks:
Figure 15.1 shows an overview of the Data Access Object Model for the Jet 3.0 database engine. At the top of the hierarchy is the Microsoft Jet database engine, referred to as the DBEngine object. The DBEngine object contains all the other objects that are part of the hierarchy. It is the only object that does not have an associated collection.
Figure 15.1. An overview of the Data Access Object Model.
Each object within the Data Access Object Model is important to you because you will manipulate the various objects at runtime using code so that you can accomplish the tasks required by your application. The following is a description of each major object and how it affects you in your programming endeavors.
The Workspaces collection contains Workspace objects. Each Workspace object defines the area within which a particular user operates. All security and transaction processing for a given user takes place within a particular Workspace. You can programmatically create multiple Workspaces. This is of great value because, by using this technique, you can log in as another user behind the scenes and accomplish tasks not allowed by the security level of the current user. For example, you can log in as a member of the Admins group, change the structure of a table that the current user does not have rights to, and log back out without the user of the system ever knowing that anything happened.
The Users collection contains the User objects for a particular Workspace. Each User object is a user account defined by a workgroup database. Because each user is a member of one or more groups, each User object contains a Groups collection that consists of each group that a particular user is a member of. User objects can be easily added and manipulated at runtime.
The Groups collection contains all Group objects for a particular Workspace. Each Group object is a group defined by a workgroup database. Because each group contains users, the Group object contains a Users collection that consists of each user who is a member of the group. Like User objects, Group objects can be added and manipulated at runtime.
The Databases collection contains all the databases that are currently open within a particular Workspace. Multiple databases can be opened at a time. These open databases can be either Jet databases or external databases. A Database object refers to a particular database within the Databases collection. It is easy to loop through the Databases collection, printing the name of each Database object contained within the collection. The code looks like this:
Sub EnumerateDBs()
Dim ws As Workspace
Dim db As DATABASE
Dim db1 As DATABASE
Dim db2 As DATABASE
Set ws = DBEngine(0)
Set db1 = CurrentDb
Set db2 = ws.OpenDatabase("Chap9.MDB")
For Each db In ws.Databases
Debug.Print db.Name
Next db
End Sub
This code loops through each open database within the current Workspace. It prints the name of each open database. It is also easy to perform all the other tasks required to build, modify, and manipulate database objects at runtime.
This code, and all the code in this chapter, is contained in the CHAP15.MDB file included with this book's CD-ROM.
The TableDefs collection contains all the tables contained within a particular database. This includes all tables, whether they are open or not. The TableDefs collection also includes linked tables. It contains detailed information about each table. It is easy to loop through the TableDefs collection, printing various properties (for example, the name) of each Table object contained within the collection. The code looks like this:
Sub EnumerateTables()
Dim db As DATABASE
Dim tbl As TableDef
Set db = CurrentDb
For Each tbl In db.TableDefs
Debug.Print tbl.Name
Next tbl
End Sub
This code loops through each TableDef in the current database. It prints the name of each table in the database. It is easy to write code that adds, deletes, modifies, and otherwise manipulates tables at runtime.
The QueryDefs collection contains all the queries contained within a particular database. It contains information about each query. It is easy to loop through the QueryDefs collection, printing various pieces of information about each query. The code looks like this:
Sub EnumerateQueries()
Dim db As DATABASE
Dim qry As QueryDef
Set db = CurrentDb
For Each qry In db.QueryDefs
Debug.Print qry.Name
Debug.Print qry.SQL
Next qry
End Sub
This code loops through each QueryDef in the current database. It prints the name and SQL statement associated with each QueryDef. It is easy to write code that adds, deletes, modifies, and otherwise manipulates queries at runtime.
Fields collections are contained within the TableDef, QueryDef, Index, Relation, and Recordset objects. The Fields collection of an object is the collection of Field objects within the parent object. For example, a TableDef object contains Field objects that are contained within the specific table. Using the parent object, you can obtain information about its Fields collection. Here's an example:
Sub EnumerateFields()
Dim db As DATABASE
Dim tbl As TableDef
Dim fld As Field
Set db = CurrentDb
For Each tbl In db.TableDefs
For Each fld In tbl.Fields
Debug.Print fld.Name
Debug.Print fld.Type
Next fld
Next tbl
End Sub
This code loops through each TableDef in the current database. As it loops through each TableDef, it prints the name and type of each field contained within the Fields collection of the TableDef. Code can also be used to add, delete, or change the attributes of fields at runtime.
Access queries can contain parameters. These parameters are created so that the user can supply information required by the query at runtime. Each QueryDef object has a Parameters collection, which consists of Parameter objects. You can write code to manipulate these parameters at runtime. The code looks like this:
Sub EnumerateParameters()
Dim db As DATABASE
Dim qry As QueryDef
Dim prm As Parameter
Set db = CurrentDb
For Each qry In db.QueryDefs
Debug.Print "*****" & qry.Name & "*****"
For Each prm In qry.PARAMETERS
Debug.Print prm.Name
Next prm
Next qry
End Sub
This code loops through each QueryDef object within the current database. It prints the name of the QueryDef object and then loops through its Parameters collection, printing the name of each parameter. Parameter objects can be added, deleted, and manipulated through code at runtime.
Recordset objects exist only at runtime. A Recordset object is used to reference a set of records coming from one or more tables. The Recordsets collection contains all the Recordset objects that are currently open within the current Database object. Recordset objects are covered extensively later in this chapter.
The Relations collection contains all the Relation objects that describe the relationships established within a Database object. The following code loops through the current database, printing the Table and ForeignTable of each Relation object:
Sub EnumerateRelations()
Dim db As DATABASE
Dim rel As Relation
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print rel.TABLE & " Related To: " & rel.ForeignTable
Next rel
End Sub
Relationships can be created, deleted, and modified at runtime using VBA code.
The Containers collection contains information about each saved Database object. Using the Containers collection, you can manipulate all the objects contained within the current database. Consider the following code:
Sub EnumerateContainers()
Dim db As DATABASE
Dim cnt As Container
Set db = CurrentDb
For Each cnt In db.Containers
Debug.Print cnt.Name
Next cnt
End Sub
This code loops through the Containers collection, printing the name of each Container object. The results are Databases, Forms, Modules, Relationships, Reports, Scripts, SysRel, and Tables.
A Document object represents a specific object in the Documents collection. You can loop through the Documents collection of a Container object. The code looks like this:
Sub EnumerateForms()
Dim db As DATABASE
Dim cnt As Container
Dim doc As Document
Set db = CurrentDb
Set cnt = db.Containers!Forms
For Each doc In cnt.Documents
Debug.Print doc.Name
Next doc
End Sub
This code points a Container object to the forms within the current database. It then loops through each document in the Container object, printing the name of each Document object (in this case the name of each form).
It is important to understand the difference between the Forms container and the Forms collection. The Forms container is part of the Containers collection. It contains all the forms that are part of the database. The Forms collection contains all the forms open at runtime. The properties of each form in the Forms container differ from the properties of a form in the Forms collection.
Each data access object has a Properties collection. The Properties collection of an object is a list of properties associated with that particular object. You can view or modify the properties of an object using its Properties collection. The code looks like this:
Sub EnumerateProperties()
Dim db As DATABASE
Dim cnt As Container
Dim doc As Document
Dim prp As Property
Set db = CurrentDb
Set cnt = db.Containers!Forms
For Each doc In cnt.Documents
Debug.Print doc.Name
For Each prp In doc.Properties
Debug.Print prp.Name & " = " & prp.Value
Next prp
Next doc
End Sub
This code loops through each form in the current database, printing all the properties of each Form object.
The Errors collection consists of Error objects. An Error object contains information about the most recent error that occurred. Each time an operation generates an error, the Errors collection is cleared of any previous errors. Sometimes a single operation can cause more than one error. For this reason, one or more Error objects might be added to the Errors collection when a single data access error occurs.
As mentioned, the DBEngine object refers to the Jet database engine, which is at the top of the data access object hierarchy. The DBEngine object contains only two collections: Workspaces and Errors. When referring to the current database, you can use the CurrentDB() function discussed in the next section. When referring to any database other than the current database, you must refer to the DBEngine object, as in the following example:
Sub ReferToCurrentDB()
Dim ws As Workspace
Dim db As DATABASE
Set ws = DBEngine(0)
Set db = ws.OpenDatabase("Chap11")
Debug.Print db.Version
End Sub
This code creates a Workspace object variable that points at the current Workspace. The OpenDatabase method of the Workspace object is then used to open another database. The version of the database is printed by the routine.
Microsoft offers a shortcut that you can use when creating an object variable that points to the current database. Using the CurrentDB() function, you do not need to first point at the Workspace; nor do you need to issue the OpenDatabase method. Instead, you set the Database object variable equal to the result from the CurrentDB() function. The code looks like this:
Sub UseCurrentDBFunc()
Dim db As DATABASE
Set db = CurrentDb()
Debug.Print db.Version
End Sub
This code sets the Database object variable so that it points at the current database object. The CurrentDB() function cannot be used to refer to objects that are not part of the current database.
A Recordset object is used to represent the records in a table or the records returned by a query. A Recordset object can be a direct link to the table, a dynamic set of records, or a snapshot of the data at a certain time. Recordset objects are used to directly manipulate data in a database. They let you add, edit, delete, and move through data as required by your application. Access 95 supports three types of Recordset objects: Table, Dynaset, and Snapshot.
A Recordset object of the Dynaset type can be used to manipulate local or linked tables or the results of queries. A Dynaset is actually a set of references to table data. Using a Dynaset, you can extract and update data from multiple tables, even tables from other databases. In fact, the tables whose data is included in a Dynaset can even come from databases that are not of the same type (for example, Microsoft SQL Server, FoxPro, Paradox, and dBASE).
True to its name, a Dynaset is a dynamic set of records. This means that changes made to the Dynaset are reflected in the underlying tables, and changes made to the underlying tables by other users of the system are reflected in the Dynaset. Although a Dynaset is not the fastest type of Recordset object, it is definitely the most flexible.
A Recordset object of the Snapshot type is similar to a Dynaset. The major difference is that the data included in the Snapshot is fixed at the time that it is created. The data within the Snapshot, therefore, cannot be modified and is not updated when other users make changes to the underlying tables. This trait can be an advantage or disadvantage. It is a disadvantage, of course, if it is necessary for the data in the recordset to be updatable. It is an advantage if you are running a report and want to ensure that the data does not change during the time in which the report is being run. You can therefore create a Snapshot and build the report from the Snapshot object.
With small result sets, Snapshots are more efficient than Dynasets because, by nature, a Snapshot object creates less processing overhead. Regardless of their reduced overhead, Snapshots are actually less efficient than Dynasets when returning a result set with a large volume of data (generally over 500 records). This is because when you create a Snapshot object, all fields are returned to the user as each record is accessed. On the other hand, a Dynaset object contains a set of primary keys for the records in the result set. The other fields are returned to the user only when they are required for editing or display.
A Recordset object of the Table type is often used to manipulate local or linked tables created using Microsoft Access or the Jet database engine. When you open a Table type of recordset, all operations are performed directly on the table.
Certain operations, such as a Seek, can be performed only on a Table type of recordset. You get the best performance for sorting and filtering records when using a Table type of recordset.
The down side of a Table type of recordset is that it can contain the data from only one table. It cannot be opened using a Join or Union query. It also cannot be used with tables created using engines other than Jet (for example, ODBC and other ISAM data sources).
Deciding which type of recordset to use involves looking at the task to determine which type of recordset is most appropriate. When fast searching is most important and it is not a problem to retrieve all the records, a Table is the best choice. If you must retrieve the results of a query and your result set needs to be editable, a Dynaset is the best choice. If there is no need for the results to be updated but the results must consist of a relatively small subset of the data, a Snapshot is most appropriate.
Like other objects, Recordset objects have properties and methods. The properties are the attributes of the Recordset objects, and the methods are the actions that you can take on the Recordset objects. Some properties are read only at runtime; others can be read from and written to at runtime.
When working with a recordset, you must first create a Recordset variable. The OpenRecordSet method is used to create a Recordset object variable. You must first declare a generic Recordset variable and then point a specific recordset at the variable using a Set statement. The code looks like this:
Sub OpenTable()
Dim dbInfo As DATABASE
Dim rstClients As Recordset
Set dbInfo = CurrentDb()
Set rstClients = dbInfo.OpenRecordset("tblClients")
Debug.Print rstClients.Updatable
End Sub
This code creates a Database object variable and a Recordset object variable. It then uses the CurrentDB function to point the Database object variable to the current database. Next, it uses the OpenRecordSet method to assign the recordset based on tblClients to the object variable rstClients.
The type of recordset that is created is determined by the default type for the object or by a second parameter of the OpenRecordSet method. If the OpenRecordSet method is executed on a table and no second parameter is specified, the recordset is opened as the Table type. If the OpenRecordSet method is performed on a query and no second parameter is specified, the recordset is opened as the Dynaset type. This default behavior can be overridden by passing a second parameter to the OpendRecordSet method. The code looks like this:
Sub OpenDynaSet()
Dim dbInfo As DATABASE
Dim rstClients As Recordset
Set dbInfo = CurrentDb()
Set rstClients = dbInfo.OpenRecordset("tblClients", dbOpenDynaset)
Debug.Print rstClients.Updatable
End Sub
This code opens the recordset as a Dynaset. dbOpenTable, dbOpenDynaset, and dbOpenSnapshot are all intrinsic constants that can be used to open a Recordset object. A query can be opened only as a Dynaset or Snapshot Recordset object. The code to open a recordset based on a query appears as follows:
Sub OpenQuery()
Dim dbInfo As DATABASE
Dim rstClients As Recordset
Set dbInfo = CurrentDb()
Set rstClients = dbInfo.OpenRecordset("qryHoursByProject", dbOpenSnapshot)
Debug.Print rstClients.Updatable
End Sub
It is important to note that the proper method to create a Recordset object in Access 95 differs from that of earlier versions of Access. In earlier versions of Access, it was appropriate to dimension a Dynaset, Snapshot, or Table type of object variable and then use the CreateDynaset, CreateSnapshot, and OpenTable methods of the Database object to create the appropriate type of recordset. This method for creating recordsets is included in Access 95 for backward compatibility only. It should be avoided and replaced with the code included in this section.
Microsoft provides several arguments that control the way in which a recordset is opened. Let's go over the arguments and their uses:
The arguments described can be used in combination to accomplish the desired objectives. The following example shows the use of a OpenRecordset argument:
Sub OpenRecordsetArgs()
Dim db As DATABASE
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjects", dbOpenDynaset, dbReadOnly)
Debug.Print rst.Updatable
End Sub
This code opens a recordset as read-only.
When you have a Recordset object variable set, you probably want to manipulate the data in the recordset. You can use several methods to traverse through the records in a recordset:
Here are some examples:
Sub RecordsetMovements()
Dim db As DATABASE
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjects", dbOpenDynaset)
Debug.Print rst!ProjectID
rst.MoveNext
Debug.Print rst!ProjectID
rst.MoveLast
Debug.Print rst!ProjectID
rst.MovePrevious
Debug.Print rst!ProjectID
rst.MoveFirst
Debug.Print rst!ProjectID
rst.Close
End Sub
This code opens a Dynaset. The record pointer is automatically placed on the first record of the Dynaset when the recordset is opened. The routine prints the contents of the ProjectID field and then moves to the next record, printing its ProjectID. It then moves to the last record of the Dynaset, printing its ProjectID; moves to the previous record, printing its ProjectID; and then moves to the first record, printing its ProjectID. The Close method is applied to the Recordset object. The Close method properly closes the recordset, ensuring that all changes are written to disk.
Before you begin to traverse through recordsets, you need to understand two Recordset properties: BOF and EOF. These properties are used to determine whether you have reached the limits of your recordset. The BOF property is True when the record pointer is before the first record, and the EOF property is True when the record pointer is after the last record. Here is a code sample that shows the use of the EOF property:
Sub DetermineLimits()
Dim db As DATABASE
Dim rstClients As Recordset
Set db = CurrentDb()
Set rstClients = db.OpenRecordset("tblClients", dbOpenSnapshot)
Do While Not rstClients.EOF
Debug.Print rstClients![ClientID]
rstClients.MoveNext
Loop
rstClients.Close
End Sub
This code traverses through a Snapshot recordset, printing the value of the ClientID field for each record until it reaches the position after the last record in the recordset. It then exits the loop and closes the recordset.
You need to keep in mind some important characteristics of the BOF and EOF properties:
The RecordCount property of a recordset returns the number of records in a recordset that have been accessed. The problem with this is evident if you open a recordset and view the RecordCount property. You will discover that the count is equal to 0 if there are no records in the recordset or equal to 1 if there are records in the recordset. The record count becomes accurate only if you visit all the records in the recordset. This can be done using the MoveLast method:
Sub CountRecords()
Dim db As DATABASE
Dim rstProjects As Recordset
Set db = CurrentDb()
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenSnapshot)
Debug.Print rstProjects.RecordCount 'Prints 0 Or 1
rstProjects.MoveLast
Debug.Print rstProjects.RecordCount 'Prints an accurate record Count
rstProjects.Close
End Sub
The MoveLast method has its problems. It is slow and inefficient, especially in a client/server environment. Furthermore, in a network environment, the RecordCount property becomes inaccurate as people add and remove records from the table. This means that if determining the record count is not absolutely necessary, you should avoid it. The RecordCount property has one good use, though: It can be used to see whether there are any records in a recordset. If you are performing an operation that might return an empty recordset, you can easily use the RecordCount property to determine whether records were returned.
Sometimes you might need to sort or filter an existing recordset. You also might want to locate each record in the recordset that meets some specified criteria. The following techniques allow you to sort, filter, and find records within a Recordset object.
You can't actually change the sort order of an existing Dynaset or Snapshot. Instead, you create a second recordset based on the first recordset. The second recordset is sorted in the desired order. It works like this:
Sub SortRecordset()
Dim db As DATABASE
Dim rstTimeCardHours As Recordset
Set db = CurrentDb
Set rstTimeCardHours = db.OpenRecordset("tblTimeCardHours", dbOpenDynaset)
Debug.Print "NOT Sorted!!!"
Do While Not rstTimeCardHours.EOF
Debug.Print rstTimeCardHours![DateWorked]
rstTimeCardHours.MoveNext
Loop
Debug.Print "Now Sorted!!!"
rstTimeCardHours.Sort = "[DateWorked]"
Set rstTimeCardHours = rstTimeCardHours.OpenRecordset
Do While Not rstTimeCardHours.EOF
Debug.Print rstTimeCardHours![DateWorked]
rstTimeCardHours.MoveNext
Loop
End Sub
In this case, you are sorting a Dynaset that is based on the table tblTimeCardHours. The first time you loop through the recordset and print each date worked, the dates are in the default order (usually the primary key order). After using the Sort method to sort the recordset, the records appear in order by the date worked.
Filtering an existing recordset is similar to sorting one. The following example is a variation of the previous example. Instead of sorting an existing recordset, it filters the existing recordset.
Sub FilterRecordSet()
Dim db As DATABASE
Dim rstTimeCardHours As Recordset
Set db = CurrentDb
Set rstTimeCardHours = db.OpenRecordset("tblTimeCardHours", dbOpenDynaset)
Debug.Print "Without Filter"
Do While Not rstTimeCardHours.EOF
Debug.Print rstTimeCardHours![DateWorked]
rstTimeCardHours.MoveNext
Loop
rstTimeCardHours.Filter = "[DateWorked] Between #1/1/95# and #1/5/95#"
Debug.Print "With Filter"
Set rstTimeCardHours = rstTimeCardHours.OpenRecordset
Do While Not rstTimeCardHours.EOF
Debug.Print rstTimeCardHours![DateWorked]
rstTimeCardHours.MoveNext
Loop
End Sub
The first time the code loops through the recordset, no filter is set. The filter is set and the code loops through the recordset again. The second time, only the records meeting the filter criteria are displayed.
The Seek method enables you to find records in a Table recordset. It is usually the quickest method of locating data because it uses the current index to locate the requested data. It works like this:
Sub SeekProject(lngProjectID As Long)
Dim db As DATABASE
Dim rstProjects As Recordset
Set db = CurrentDb()
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenTable)
rstProjects.INDEX = "PrimaryKey"
rstProjects.Seek "=", lngProjectID
If rstProjects.NoMatch Then
MsgBox lngProjectID & " Not Found"
Else
MsgBox lngProjectID & " Found"
End If
End Sub
This code uses the primary key index to locate the first project with the project number that was passed to the function. It then displays a message box to indicate whether the value was found.
The Seek method cannot be used to locate data in a Dynaset or Snapshot. Furthermore, it cannot be used to search for records in an attached table, regardless of whether the attached table is an Access table or a client/server table. In this case, you must use the FindFirst, FindLast, FindNext, and FindPrevious methods. The FindFirst method finds the first occurrence of data that meets the criteria, and FindLast finds the last occurrence of such data. The FindNext and FindPrevious methods enable you to find additional occurrences of the data.
Sub FindProject(lngValue As Long)
Dim db As DATABASE
Dim rstProjects As Recordset
Dim sSQL As String
Set db = CurrentDb()
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)
sSQL = "[ProjectID] = " & lngValue
rstProjects.FindFirst sSQL
If rstProjects.NoMatch Then
MsgBox lngValue & " Not Found"
Else
MsgBox lngValue & " Found"
End If
End Sub
This code uses the FindFirst method to find the first occurrence of the parameter that was passed in. Again, it displays an appropriate message box.
You can use another trick to search a linked table. You can open the database that contains the linked table and seek directly on the table data.
The AbsolutePosition property returns the position of the current record. It is a zero-based value. It can be used to specify where in a recordset a specific record was found. Here's an example:
Sub FindPosition(lngValue As Long)
Dim db As DATABASE
Dim rstProjects As Recordset
Dim sSQL As String
Set db = CurrentDb()
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)
sSQL = "[ProjectID] = " & lngValue
rstProjects.FindFirst sSQL
If rstProjects.NoMatch Then
MsgBox lngValue & " Not Found"
Else
Debug.Print rstProjects.AbsolutePosition
End If
End Sub
This code finds the first record with a ProjectID equal to the long integer received as a parameter. If the ProjectID is found, the value in the AbsolutePosition property of the record is printed.
Do not rely on the fact that the AbsolutePosition of a particular record will stay the same. The AbsolutePosition of a record changes as records are added or deleted or their order is changed as the records are modified.
A Bookmark is a system-generated byte array that uniquely identifies each record in a recordset. The Bookmark property of a recordset changes as you move to each record in the recordset. It is often used if you need to store the current position in the recordset so that you can perform some operation and then return to the position after the operation is completed. Three steps are involved in this process:
The operation looks like this in code:
Sub UseBookMark()
Dim db As DATABASE
Dim rstProjects As Recordset
Dim sSQL As String
Dim vntPosition As Variant
Set db = CurrentDb()
Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)
vntPosition = rstProjects.Bookmark
Do Until rstProjects.EOF
Debug.Print rstProjects!ProjectID
rstProjects.MoveNext
Loop
rstProjects.Bookmark = vntPosition
Debug.Print rstProjects!ProjectID
End Sub
This code begins by opening a recordset and storing the Bookmark of the first record into a Variant variable. It then loops through each record in the recordset, printing the value within the ProjectID. After the loop has completed, the Bookmark property of the recordset is set equal to the Variant variable, setting the current position of the recordset back to where it was before the loop began processing.
The RecordsetClone property of a form is used to refer to the recordset underlying the form. This property is often used when you want to perform an operation and then synchronize the form with its underlying recordset. Here's an example:
Private Sub cmdFindClient_Click()
Me.RecordsetClone.FindFirst "ClientID = " & Me!txtClientID
If Me.RecordsetClone.NoMatch Then
MsgBox Me!txtClientID & " Not Found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub
This routine performs the FindFirst method on the RecordsetClone of the current form. If the record is found, the Bookmark property of the form is set equal to the Bookmark of the recordset. This matches the form's position to the underlying recordset's position.
Access Parameter queries are very powerful. They enable the user to specify criteria at runtime. This ability is often helpful if your user wants to fill out a form at runtime and have the values on that form fed to the query. Consider the following code:
Sub RunParameterQuery(datStart As Date, datEnd As Date)
Dim db As DATABASE
Dim qd As QueryDef
Dim rs As Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("qryBillAmountByClient")
qd.PARAMETERS("Please Enter Start Date") = datStart
qd.PARAMETERS("Please Enter End Date") = datEnd
Set rs = qd.OpenRecordset
Do While Not rs.EOF
Debug.Print rs![CompanyName], rs![BillAmount]
rs.MoveNext
Loop
End Sub
This subroutine receives two date variables as parameters. It could just as easily receive form controls as parameters. It opens a query definition called qryBillAmountByClient. It then sets the values of the parameters called Please Enter Start Date and Please Enter End Date to the date variables passed into the subroutine as parameters. The query is then executed by issuing the OpenRecordset method on the Recordset object.
So far, you have learned how to loop through and work with Recordset objects. Now you will learn how to change the data contained in a recordset.
Often, you want to loop through a recordset, modifying all the records that meet a specific set of criteria. The code required to accomplish this task looks like this:
Sub IncreaseEstimate()
Dim db As DATABASE
Dim rstProjectst As Recordset
Dim sSQL As String
Dim intUpdated As Integer
Set db = CurrentDb()
Set rstProjectst = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)
sSQL = "ProjectTotalEstimate < 30000"
intUpdated = 0
rstProjectst.FindFirst sSQL
Do While Not rstProjectst.NoMatch
intUpdated = intUpdated + 1
rstProjectst.Edit
rstProjectst!ProjectTotalEstimate = rstProjectst!ProjectTotalEstimate _
* 1.1
rstProjectst.UPDATE
rstProjectst.FindNext sSQL
Loop
Debug.Print intUpdated & " Records Updated"
rstProjectst.Close
End Sub
This code finds the first record with a ProjectTotalEstimate less than 30,000. It uses the Edit method to ready the current record in the Dynaset for editing. It replaces the ProjectTotalEstimate with the ProjectTotalEstimate multiplied by 1.1. It then issues the Update method to write the changes to disk. Finally, it uses the FindNext method to locate the next occurrence of the criteria.
Many of the tasks that you can perform by looping through a recordset can also be accomplished with an Update query. Executing an Update query is often more efficient than the process of looping through a recordset. If nothing else, it takes much less code. Therefore, it is important to understand how to execute an Update query through code.
Let's assume that you have a query called qryChangeTotalEstimate that increases the ProjectTotalEstimate for all projects where the ProjectTotalEstimate is less than 30,000. The query is an Update query. The following code executes the stored query definition:
Sub RunUpdateQuery()
Dim db As DATABASE
Dim qd As QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("qryIncreaseTotalEstimate")
qd.Execute
End Sub
Notice that the Execute method operates on the query definition, executing the Action query.
The Delete method enables you to programmatically delete records from a recordset. It works like this:
Sub DeleteCusts(lngProjEst As Long)
Dim db As DATABASE
Dim rstProjects As Recordset
Dim intCounter As Integer
Set db = CurrentDb
Set rstProjects = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)
intCounter = 0
Do While Not rstProjects.EOF
If rstProjects!ProjectTotalEstimate < lngProjEst Then
rstProjects.Delete
intCounter = intCounter + 1
End If
rstProjects.MoveNext
Loop
Debug.Print intCounter & " Customers Deleted"
End Sub
This code loops through the rstProjects recordset. If the ProjectTotalEstimate amount is less than the value passed in as a parameter, the record is deleted. This task could also be accomplished with a Delete query.
The AddNew method enables you to programmatically add records to a recordset. Here's an example:
Private Sub cmdAddRecord_Click()
Dim db As DATABASE
Dim rstProject As Recordset
Set db = CurrentDb()
Set rstProject = db.OpenRecordset("tblProjectsChange", DB_OPEN_DYNASET)
With rstProject
.AddNew
!ProjectName = Me!txtProjectName
!ProjectDescription = Me!txtProjectDescription
![ClientID] = Me!cboClientID
.UPDATE
End With
Me!txtProjectID = rstProject!ProjectID
End Sub
This code is used on an Unbound form called frmUnbound. The code issues an AddNew method, which creates a buffer ready to accept data. Each field in the recordset is then populated with the values from the controls on the form. The Update method writes the data to disk. If you forget to include the Update method, the record is never written to disk. The last line of code is there to illustrate a problem: When an AddNew method is issued, the record pointer is never moved within the Dynaset. Even after the Update method is issued, the record pointer remains at the record it was on prior to the AddNew. You must explicitly move to the new record before populating the txtProjectID text box with the ProjectID from the recordset. This can easily be accomplished using the LastModified property, covered in the next section.
The LastModified property contains a Bookmark of the most recently added or modified record. By setting the Bookmark of the recordset to the LastModified property, the record pointer is moved to the most recently added record. The code looks like this:
Private Sub cmdLastModified_Click()
Dim db As DATABASE
Dim rstProject As Recordset
Set db = CurrentDb()
Set rstProject = db.OpenRecordset("tblProjectsChange", DB_OPEN_DYNASET)
With rstProject
.AddNew
!ProjectName = Me!txtProjectName
!ProjectDescription = Me!txtProjectDescription
![ClientID] = Me!cboClientID
.UPDATE
.Bookmark = rstProject.LastModified
End With
Me!txtProjectID = rstProject!ProjectID
End Sub
Notice that the Bookmark of the recordset is set to the LastModified property of the recordset.
In 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. This can all be accomplished by manipulating the various data access objects.
Many properties and methods are available for adding and modifying Jet engine objects. The following code creates a table, adds some fields, and then adds a primary key index:
Sub CreateTable()
Dim db As Database
Dim td As TableDef
Dim fld As Field
Dim idx As Index
Set db = CurrentDb()
' Create new TableDef.
Set td = db.CreateTableDef("tblFoods")
' Add field to Table Definition
Set fld = td.CreateField("FoodID", DB_TEXT, 5)
td.Fields.Append fld
Set fld = td.CreateField("Description", DB_TEXT, 25)
td.Fields.Append fld
Set fld = td.CreateField("Calories", DB_INTEGER)
td.Fields.Append fld
db.TableDefs.Append td
'Designate the FoodID field as the Primary Key Index
Set idx = td.CreateIndex("PrimaryKey")
Set fld = idx.CreateField("FoodID")
idx.Primary = True
idx.Unique = True
idx.Fields.Append fld
'Add the index to the Indexes collection
td.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. Three fields are added to the table. Notice that the field name, type, and length are specified. After the table definition has been added to the database, indexes can be added to the table. The index added in the example is a primary key index.
Just as you can add a table using code, you can remove a table using code. The code looks like this:
Sub DeleteTable()
Dim db As DATABASE
Set db = CurrentDb
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.
If you are creating tables using code, you probably want to establish relationships between those tables using code. Here's how:
Sub CreateRelation()
Dim db As DATABASE
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
Set rel = db.CreateRelation()
With rel
.Name = "PeopleFood"
.TABLE = "tblFoods"
.ForeignTable = "tblPeople"
.Attributes = dbRelationDeleteCascade
End With
Set fld = rel.CreateField("FoodID")
fld.ForeignName = "FoodID"
rel.Fields.Append fld
db.Relations.Append rel
End Sub
This code begins by setting a Relation object to a new relationship. It then populates the Name, Table, Foreign Table, and Attributes properties of the relationship. After the properties of the relationship have been set, the field is added to the Relation object. Finally, the Relationship object is appended to the Relations collection.
If you are running your application from the Access Developer's Toolkit version of the product, 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, using code, after the user has designed them. The code needed to build a query looks like this:
Sub CreateQuery()
Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryBigProjects")
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.
It is important to understand that unlike the CreateQueryDef method of the database object, which immediately adds the query definition to the database, the CreateTableDef method does not immediately add the table definition to the database. The Append method of the TableDefs collection must be used to actually add the table definition to the database.
You can create a temporary query definition by using a zero-length string for the name argument of the CreateQueryDef method.
A Container object maintains information about saved Database objects. The types of objects that are within the Containers collection are Databases, Tables (included Queries), Relationships, SysRel, Forms, Reports, Scripts (Macros), and Modules. The Container object is responsible for letting Jet know about the user interface objects. Databases, Tables, Relationships, and SysRel all have Jet as their parent object. Forms, Reports, Scripts, and Modules all have the Access application as their parent object.
Each Container object possesses a collection of Document objects. These are the actual forms, reports, and other objects that are part of your database. The Document objects contain only summary information about each object (date created, owner, and so on); they do not contain the actual data of the objects. To refer to a particular document within a container, you must use one of two techniques:
Containers("Name")
or
Containers!Name
To list each Container object and its associated Document objects, you need the following code:
Sub ListAllDBObjects()
Dim db As DATABASE
Dim con As Container
Dim doc As Document
Set db = CurrentDb
For Each con In db.Containers
Debug.Print "*** " & con.Name & " ***"
For Each doc In con.Documents
Debug.Print doc.Name
Next doc
Next con
End Sub
This code loops through all the containers, and within each container, all of the documents, listing each one.
The potential applications for the methodologies learned in this chapter are endless. This section explores just a few of the potential applications of the techniques.
The objective of this section is to create a Report Selection dialog. The dialog will contain a list box to display the name of every report in the database. When the user has selected a report name and has clicked on the Preview Report button, the selected report runs. The form is shown in Figure 15.2.
Figure 15.2. The Report Selection dialog.
The trick here is the population of the list box that displays all the reports in the database. Chapter 13, "Let's Get More Intimate With Forms: Advanced Techniques" covers how to populate a list box using a callback function. Here, you learn how the callback function utilizes the Document collection to identify all the reports contained in the database.
The acLBIntialize case of the Case statement uses the Containers collection of the Database object to loop through each Report object and add its name to the list box. When the list box has been loaded, the user can select any report in the list. The code under the Click event of the Preview Report button uses the value of the selected item in the list box as an argument to the OpenReport method of the DoCmd object. If you use this technique of report selection, you must ensure that any reports needing to be run from forms that populate parameter queries underlying the reports either cancel themselves or load the required form when they are run.
Function FillWithReportList(ctl As Control, vntID As Variant, _
lngRow As Long, lngCol As Long, intCode As Integer) _
As Variant
Dim db As DATABASE
Dim cnt As Container
Dim doc As Document
Dim intCounter As Integer
Static sastrReports() As String
Static sintNumReports As Integer
Dim varRetVal As Variant
varRetVal = Null
Select Case intCode
Case acLBInitialize ' Initialize.
Set db = CurrentDb
Set cnt = db.Containers!Reports
sintNumReports = cnt.Documents.Count
ReDim sastrReports(sintNumReports - 1)
For Each doc In cnt.Documents
sastrReports(intCounter) = doc.Name
intCounter = intCounter + 1
Next doc
varRetVal = sintNumReports
Case acLBOpen 'Open
varRetVal = Timer 'Generate unique ID for control.
Case acLBGetRowCount 'Get number of rows.
varRetVal = sintNumReports
Case acLBGetColumnCount 'Get number of columns.
varRetVal = 1
Case acLBGetColumnWidth 'Get column width.
varRetVal = -1 '-1 forces use of default width.
Case acLBGetValue 'Get the data.
varRetVal = sastrReports(lngRow)
End Select
FillWithReportList = varRetVal
End Function
At times you might want to disable the default record movement and add, edit, or delete functionality from a form and code all the functionality yourself. You might want to do this when you are going against client/server data and want to execute additional control over the data-entry environment. You also might want to use these techniques when you are developing applications for both the Access and Visual Basic environments and are striving for maximum code compatibility. Regardless of your reasons for employing the following techniques, it is a good idea to know how to utilize a form's underlying recordset to display and modify data.
Figure 15.3 shows a form in which the Navigation Buttons and Record Selectors have been removed. The form contains six command buttons: Move Previous, Move Next, Add, Delete, Find, and Exit. All the buttons utilize the recordset underlying the form to move from record to record within the form and modify the data contained within the form. The code behind the Move Previous button looks like this:
Private Sub cmdPrevious_Click()
Me.RecordsetClone.MovePrevious
If Me.RecordsetClone.BOF Then
Me.RecordsetClone.MoveNext
MsgBox "Already at First Record!!"
End If
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
Figure 15.3. Removal of Navigational Buttons and Record Selectors.
This routine performs the MovePrevious method on the RecordsetClone property of the form. If the BOF property becomes True, indicating that the record pointer is before the first valid record, the MoveNext method is performed on the RecordsetClone of the form to return the record pointer to the first record in the recordset. Finally, the Bookmark of the form is synchronized with the Bookmark of the RecordsetClone. The code behind the Move Next button is similar:
Private Sub cmdNext_Click()
Me.RecordsetClone.MoveNext
If Me.RecordsetClone.EOF Then
Me.RecordsetClone.MovePrevious
MsgBox "Already at Last Record!!"
End If
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
The code behind the Add Button is a little tricky. It looks like this:
Private Sub cmdAdd_Click() Me.RecordsetClone.AddNew Me.RecordsetClone!CompanyName = "New Company" Me.RecordsetClone.UPDATE Me.Bookmark = Me.RecordsetClone.LastModified End Sub
The Addnew method is performed on the RecordsetClone of the form. This method creates a buffer in memory, ready to accept the new data. The record pointer is not actually moved, so it remains over whatever record you were on before you began the add process. You need to write the new record to disk so that it becomes a valid record and the user can move to it. Because the CompanyName field is a required field, you must populate it with data before issuing the Update method on the RecordsetClone. Even after the Update method has been issued, the record pointer still remains over the record that the user was on before you began the Add process. By setting the Bookmark of the form to the LastModified property of the recordset, you synchronize the form with the new record. In a production environment, you would want to clear out all the text boxes and force the user to save or cancel before either the AddNew or Update methods are issued.
The process of deleting a record is quite simple. The code looks like this:
Private Sub cmdDelete_Click()
Dim intAnswer As Integer
intAnswer = MsgBox("Are You Sure???", vbYesNo + vbQuestion, _
"Delete Current Record?")
If intAnswer = vbYes Then
Me.RecordsetClone.Delete
Call cmdNext_Click
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub
This code verifies that the user actually wants to delete the record and then issues the Delete method on the RecordsetClone of the form. Because the current record is no longer valid, the code calls the Click event of the cmdNext button. Finally, it sets the Bookmark of the form to the Bookmark of the RecordsetClone to synchronize the form with its underlying recordset.
The last piece of code involved in the form is the code under the Find button. It looks like this:
Private Sub cmdFind_Click()
Dim strClientID As String
Dim strBookmark As String
strBookmark = Me.Bookmark
strClientID = InputBox("Enter Client ID of Client You Want to Locate")
Me.RecordsetClone.FindFirst "ClientID = " & strClientID
If Me.RecordsetClone.NoMatch Then
MsgBox "Client ID " & strClientID & " Not Found!!"
Me.Bookmark = strBookmark
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub
The routine begins by storing the Bookmark of the current record to a String variable. Users are prompted for the ClientID that they want to locate, and then the FindFirst method is issued on the RecordsetClone of the form. If no match is found, the user is warned and the Bookmark of the form is set to the value within the String variable, returning the record pointer to the position it was in prior to the search. If the ClientID is found, the Bookmark of the form is synchronized with the Bookmark of the RecordsetClone.
In this chapter, you learned how to manipulate recordsets via code. The chapter began by introducing you to the Data Access Object Model. It described the various collections within the database and showed how you can easily list the members of each collection. It then explored the different types of recordsets available within Access, highlighting why you would want to use each type. Next you learned how to manipulate recordsets using code. The ability to manipulate recordsets behind the scenes is an important aspect of the VBA language. It frees you from the user interface and allows you to control what is going on programmatically. Finally, you learned how to create and modify database objects using code. This is important if the application that you are creating requires you to create or modify tables, queries, or other objects at runtime.