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.
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).
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.
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
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.
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.
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.
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:
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.