Chapter 6: ActiveX Data Objects
Chapter 6: ActiveX Data Objects
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Ken Getz, Paul Litwin, Mike Gilbert
SYBEX Inc.
Reproduced from Access 2000 Developer's Handbook, Volume 1, by Ken Getz, Paul Litwin, and Mike Gilbert, by permission of SYBEX Inc. ISBN 0-7821-2370-8. Copyright 2000, SYBEX Inc. All rights reserved. For further information, please contact info@sybex.com, or call 1-800-227-2346, or visit their Web site at http://www.sybex.com/.
Download this article in Microsoft Word format (ACSybex6.exe, 322 KB).
5339
MSDNSamples\ACSybex6
Contents
Introduction ADO? What About DAO? Dueling Object Hierarchies ADO, UDA, and OLE DB Nuts and Bolts: Syntax Explained Using Default Collections Enumerating Objects in Collections Working with Properties Data Definition Using ADOX Working with Recordsets Meet the Cursors Creating a Recordset The Supports Method Creating Recordset Objects Moving Through a Recordset Sorting Recordsets Filtering Recordsets Refreshing Recordsets Editing Data in a Recordset Object Persisting Recordsets Using Recordsets in Place of Arrays Using Command Objects for Bulk Operations Schema Recordsets Using the CurrentProject and CurrentData Collections Designing frmDBC Choosing an Object Type and an Action Displaying the Object List Filling the Object List Summary
Introduction
No matter which program you're using as an interface to your data, at times you'll need programmatic access to your database's structure and its data. You might want to retrieve the schema of a table, create a new index, or walk through the data returned by a query, one row at a time. Perhaps you need to manipulate your application's security or find a particular row on a form. You can accomplish any of these tasks thanks to Access' use of ActiveX® Data Objects (ADO) and ADO Extensions for DDL and Security (ADOX), a pair of COM libraries that are part of Microsoft's Universal Data Access strategy for retrieving and manipulating data. In this chapter, we'll cover the basics of ADO and present some useful examples along the way.
Note
One thing's for sure: ADO is a huge topic. All we can hope to do here is to get you started, show you the big picture, and fill in some of the important details. Full coverage of ADO can, and does, fill entire volumes, and our point here is not to cover every single issue, but to cover the points you're most likely to need as an Access developer.
ADO? What About DAO?
Previous versions of Access used a different library, named Data Access Objects (DAO), for programmatic access to data. DAO began life as an interface to the Jet database engine in Access 1 and grew in size and complexity through Access 97. Although DAO is still present in Access 2000, it's no longer the preferred method for retrieving data, and it's not the best library to learn for new applications. If you need to maintain an existing application that uses DAO, see Appendix C. This appendix is a reprint of a chapter from an earlier edition of this book that provides basic coverage of DAO, parallel to this chapter's coverage of ADO.
You'll still run into DAO within Access 2000 in several situations:
- If you retrieve the recordset of a form in an Access database, you'll get a DAO recordset, not an ADO recordset. (Forms in an Access project use ADO recordsets.) See Chapter 8 for more information on forms and recordsets.
- If you convert a database from a previous version of Access, it will use DAO by default. (If you create a new database in Access 2000, it will use ADO by default.)
Note
This chapter won't dwell on converting from DAO to ADO—there's simply too much new material on ADO for us to devote major portions of the chapter to DAO as well. Where appropriate, we've pointed out important issues you'll need to watch out for if you're converting applications from previous versions of Access.
Setting References
When you create a new Access database (an MDB file) or an Access data project (an ADP file) in Access, Access assumes that you'll want to work with ADO and sets a reference for you to the Microsoft® ActiveX Data Objects 2.1 Library. If you convert an existing Access application into Access 2000 format, Access updates your original reference (that is, to DAO) up to the current version of DAO and does not add a reference to ADO to your project.
Whether Access assigns DAO or ADO by default to your database, you can choose to use the other library, either in addition to or as a replacement for the default library. To do this, from the Visual Basic® editor, choose Tools | References. This will open the References dialog box, shown in Figure 6.1. This dialog box lists the available object libraries installed on your computer. You can use objects from any of the libraries to which you've set a reference by checking its name within your application.
Figure 6.1: The References dialog box
If you select Microsoft ActiveX Data Objects 2.1 Library, your application will use ADO. If you select Microsoft DAO 3.6 Object Library, your application will use DAO.
What happens if you select both libraries within a single application? The answer is that you can use both ADO and DAO within that application. However, you need to be aware of problems that can be caused by the fact that both libraries have some objects with the same name. For example, both ADO and DAO contain RECORDSET objects. In this case, a reference to the object refers to whichever library appears first in the References dialog box (you can use the Priority buttons in the dialog box to rearrange references). So, depending on the references, a line of code such as
Dim rst As Recordset
might refer to either a DAO or an ADO recordset. You can't tell which one you'll get without checking the References dialog to see which appears first in the list.
To solve this problem, you can disambiguate (yes, that's a real word) the reference by prepending the name of the library. Such disambiguated references specify both the object name and the library name. Thus, the following two lines of code refer to two different objects:
Dim rst1 As ADODB.Recordset
Dim rst2 As DAO.Recordset
In this book, we've tried to always use disambiguated object names. You may want to adopt the same convention.
Note
Note that the name of the library isn't necessarily what you think it is; the name of the ADO library in code is ADODB. You can determine the library name by looking at the list of available libraries in the Object Browser, which is displayed when you press F2 from the Visual Basic editor.
Tip
If you're an experienced DAO developer, you may also find the Microsoft® white paper "Migrating from DAO to ADO" very helpful. You'll find this white paper on the book's accompanying CD.
Disambiguating Unleashed
Disambiguating references can never hurt. In addition, it can help make your code run a tiny bit faster. Because VBA doesn't need to peruse the entire list of libraries to determine which one contains the object you're programming, your code can run faster. Microsoft recommends that you disambiguate all references, and we do too. It's a difficult habit to get into, but it's worth it. You can carry it to extremes, if you like. That is, you could preface every VBA function call with the VBA library name. Because Access always places VBA at the top of the list of references and you can't move it lower, all VBA method references will be resolved quickly. On the other hand, if you add a reference to a COM library yourself, using the Tools | References dialog box, you should consider always adding the library name to each reference you use.
Which Library to Use?
Given the dual data access libraries available with Access 2000, how do you choose which one to use for any particular application? Access 2000 allows you to create two different kinds of applications, natively. You can create an MDB file, or you can choose to create an ADP file (an Access Data Project): in both cases, for new applications, Access uses OLE DB (through ActiveX Data Objects, or ADO) to retrieve data. If your data is stored in SQL Server™, MDB files can only link to the tables, using Jet to manage the data and its retrieval. For ADP files, however, the data comes directly from SQL Server, and Jet is never involved. If you want to work with SQL Server data, we think you'll find that ADP files give you the best development environment for your projects. If you don't need the power of SQL Server, or you need to distribute workgroup applications that don't require the overhead and maintenance of SQL Server, MDB files will work well for your applications. (For more information on the differences between MDB files and ADP files, see Access 2000 Developer's Handbook, Volume II.)
Even given the choice of project types you create, you can still choose whether you want to use ADO or DAO as the mechanism for working with data programmatically. How can you choose which is the right choice for you? Here are some guidelines:
- If you're working in an Access project (an ADP file, as opposed to an MDB file), you should definitely stick with ADO. This is the native data access library for projects, and using ADO will make it simpler to work with data already in the project.
- If you're importing data access code from an existing Access 95 or Access 97 application, that code is already using DAO. In this case, you'll need to set a reference to DAO and disambiguate object references in the existing code, at least until you can rewrite the code to use ADO.
- If you're working with an application that was converted from a previous version of Access and don't expect to make major enhancements, stick with DAO. It's not worth rewriting all of the code just to be modern. (If you convert an existing Access application from an earlier version, you won't need to change the references. Access will automatically set a reference to DAO 3.6 for you.)
- If you're building a new application in Access 2000, we recommend that you use ADO for your data access needs. If you're an experienced Access developer, learning the new material may initially be painful. But we think it's worth it in the long run, because DAO probably won't ever get the new functionality that's now being added to ADO.
Dueling Object Hierarchies
Although it can be confusing, before investigating ADO, you must realize that Access supports its own object hierarchy, in addition to that supplied by ADO. That is, Access provides a mechanism whereby you can gather information about any open page, form, report, or module; write to the Debug object; or retrieve information from the Screen object, for example. This application hierarchy is completely separate from the ADO hierarchy (see Figure 6.2).
Note
Although Access makes it possible to get to the Document Object Model (DOM), providing programmatic access to the design of data access pages, this isn't part of Access, nor is it part of ADO. We'll cover the DOM in Access 2000 Developer's Handbook, Volume II, Chapter 12.
Figure 6.2: The Access object hierarchy
Note
Saying that Access supports two object hierarchies is somewhat misleading. Actually, Access supports an infinite number of object hierarchies. It handles ADO the same way it handles the object models for Word, Excel, the Office Assistant, or any other COM component. It's just that it's hard to use Access without encountering the ADO object model, and beginning developers tend to see Access as a single product. It's not; ADO is just another of the many components available to Windows® developers. Of course, it's the most crucial component for Access developers.
The application hierarchy consists of the UI objects that Access itself maintains. These objects consist of all the open forms, reports, data access pages, and modules; the controls, sections, and class modules associated with those objects; and the Application, DoCmd, Screen, Err, and Debug objects. These objects will play only a peripheral part in this chapter because the focus is the ADO object hierarchy—the objects that can be used to manipulate the database engine, whether that engine is the Jet engine, the SQL Server, or MSDE (Microsoft Database Engine) engine. These objects are outlined in Figure 6.3.
Figure 6.3: The ADO object hierarchy
In this chapter, we'll concentrate on the use of the ADO object hierarchy to access data within Access databases—that is, databases that use the Jet database engine as opposed to Access projects, which use the newer SQL Server/MSDE engine. You'll find coverage of ADO in Access projects in Chapter 6 of Access 2000 Developer's Handbook, Volume II.
In addition to the Access and ADO object hierarchies, we'll use a third object hierarchy in this chapter as well: the ADO Extensions for DDL and Security, usually called ADOX. ADOX contains an extra set of objects that work together with the ADO objects to allow you to manipulate the structure of a database. Figure 6.4 shows the objects in the ADOX library.
Note
Although we won't cover it here, there's yet another ADO extension object model available as part of Access: JRO (Jet Replication Objects). The JRO library allows you to work programmatically with Jet replication. For more information on JRO, see Chapter 9 in Access 2000 Developer's Handbook, Volume II.
Figure 6.4: The ADOX object hierarchy
ADO, UDA, and OLE DB
The ADO object model is part of a wider Microsoft initiative, Universal Data Access (UDA). Older data access libraries such as DAO and Remote Data Objects (RDO) concentrated on retrieving data from databases containing sets of records in tables. However, Microsoft's data access designers recognized that much useful data resides in other types of storage, such as electronic mailboxes and files on disk. The idea of UDA is to use a single method to retrieve data from any data source whatsoever.
The low-level interfaces that enable this idea to work are collectively known as OLE DB. OLE DB is an extension to COM, the Component Object Model, which increasingly dictates how all applications under Microsoft Windows interact with one another. OLE DB views the world as composed of data providers, which are programs that can take data from a data source and make it available through the OLE DB interfaces, and data consumers, which can use the OLE DB interfaces to read data. Within some limits (not all OLE DB providers support all OLE DB interfaces), any data consumer can use data from any data provider.
The OLE DB interfaces are designed with a procedural programming model in mind. What ADO brings to the picture is an object model that sits on top of any OLE DB provider, allowing you to use object-oriented programming techniques to communicate with the ultimate data source. When you retrieve data in Access using OLE DB, your request flows from your application through ADO to the OLE DB provider to the data source.
Microsoft Access databases (and the bulk of the examples in this chapter) use the Microsoft Jet 4 OLE DB Provider. Microsoft Access projects (ADP files) use the Microsoft OLE DB Provider for SQL Server. However, for the most part, it doesn't matter to your ADO code which of these providers is supplying the data. In fact, you can use ADO with any OLE DB provider to manipulate data from a wide variety of data sources programmatically within an Access database (or any other VBA, Visual Basic, Java, or C++ application, for that matter).
Tip
For more information on Universal Data Access, ADO, and OLE DB, visit the Microsoft Universal Data Access Web site at http://www.microsoft.com/data.
Nuts and Bolts: Syntax Explained
To use ADO, you'll need to be able to refer to objects and use the correct syntax when building expressions. The next few sections discuss issues surrounding the various methods of using and referring to objects.
Properties and Methods
Objects have both properties and methods. Properties are attributes of objects that can normally be retrieved and (sometimes) set. For example, many objects expose a Name property, which returns the name of the object. You could use the following statement to retrieve the name of a Form object referred to by the variable frm:
strName = frm.Name
You can think of properties as the adjectives that describe objects.
Methods are actions that can be applied to objects. For example, RECORDSET objects provide the MoveNext method, which moves the current record pointer to the next record. You could use the following statement to move to the next record of the recordset referred to by the variable rst:
rst.MoveNext
You can think of methods as the verbs that act upon objects.
Using Object Variables
Through the course of this book, we make many assumptions about your knowledge of Access and VBA. This isn't a book on beginning VBA, nor on getting started with Access. We use standard variable types without explanation, assuming you'll understand statements like the following:
Dim intX as Integer
On the other hand, object variables, the basis of all your program code surrounding ADO, may be new to you.
When you create a normal variable, you're asking Access to reserve enough space to hold the information for which you've specified a data type. If you don't specify a data type, Access assumes the most space the variable might need and uses a Variant-type variable. When you create an object variable (a variable that will refer to either a user-interface object or a data access object), Access creates only a "pointer." That is, the variable it creates only refers to a real object; it's not a real object itself. It contains not the value of the object, but its memory address instead.
For example, when you write code like this:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim frm As Form
Dim ctl As Control
none of those variables actually hold any data, nor do they refer to any real objects at this point. Each variable can contain the address of some in-memory object, but doesn't at this point. To make an object variable actually refer to a real object, you must use the Set keyword. In every case, you use Set to point the variable either at a real object (which must already exist) or at an instance of the object created with the New keyword. For example, using the variables in the previous example, you might see code like this:
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set frm = Forms("frmCustomers")
Set ctl = frm.Controls("txtCompanyName")
In each case, you've made the object variable refer to an actual object (that is, it contains the memory address of that particular object). Without this step, the object variables are just placeholders, waiting to actually refer to something.
If, in the course of your code, you point a data object variable (such as a command or recordset) at an actual object, Access will destroy the reference when the variable goes out of scope. If your variable is the only reference to the object in memory, the object in memory will automatically be destroyed (that is, removed from memory). If you have qualms about Access releasing the memory your object references use, you can explicitly delete the linkage by setting the variable equal to the predefined value Nothing:
Set rst = Nothing
In addition, if you created or opened an object as part of your assignment and want to be completely overt about your intentions, you should also use the Close method of the object to close it:
Set cnn = CurrentProject.Connection
rst.open "tblCustomers", cn
.
.
.
rst.Close
Set rst = Nothing
Although these steps aren't required, and we, too, tend to count on Access for these housekeeping details, you may want to consider these options.
WARNING
It's important that you understand what happens when you set an object variable to Nothing. All this does is set the value of the object variable so that it no longer contains the address of a real object. This doesn't explicitly remove the object from memory. Under the covers, Access (using COM) removes the object from memory when no variable or internal reference to the object exists. Therefore, it's important that you do release the reference to the object when you're done with it. On the other hand, setting a variable to Nothing doesn't guarantee that the object will be removed from memory. It simply reduces the reference count by one. When the reference count is 0, the object gets removed from memory automatically.
Dimension and Set in One Statement?
If you read other developers' code, you'll often find that the code collapses the Dim and Set statements into a single statement, like this:
Dim rst As New ADODB.Recordset
instead of
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
In this case, VBA both creates a variable named rst and sets up internal code that will instantiate the object referred to by the variable when necessary. (That is, the code doesn't create the Recordset object in memory at this point—it waits until it actually needs the object in memory before creating it.) The problem with this technique is that it's not clear when your object gets initialized, especially if the object is public and might be referenced from within any module. To make it possible for VBA to instantiate the object at any time within the application and to determine if the object must be instantiated before executing the line of your code, VBA must insert internal code between each line of your code. This has, of course, detrimental effects on the speed of your running code.
We recommend that you never use the shortcut of skipping the explicit Set statement. Yes, you save a single line of code, but you pay for it with every other line of code running a tiny bit slower. Although there may be extenuating circumstances, where the single-line declaration works for you, we'd suggest you avoid it unless you have a specific reason to use it.
The Connection Object
The Connection object is the top-level object in the ADO library. To refer to any object in the ADO hierarchy, you must specify the Connection that will be used to retrieve the data. This Connection object corresponds to a single connection to an OLE DB data provider. You can either explicitly create the Connection object, or you can implicitly create it by supplying the connection information when you create a subsidiary object such as a recordset. You can think of a Connection object as providing a way for Access to find the data it needs. It includes information about the data source (is it an MDB file, a SQL Server database, or what?) and where that data is located (on a particular server, or in a particular path?).
If you're using ADO from outside Access, you have no choice: you must create a Connection object, either implicitly or explicitly. From within Access, you can and should use the CurrentProject object to obtain a reference to the current database's connection. This object is automatically created by Access when you open a database, and it has a Connection property that you can use to retrieve a reference to the ADO connection that Access itself is using. To reference objects that aren't part of the current database, however, you'll need to create a Connection object even from within Access.
Note
We could list, in this chapter, all the properties and methods of each object. However, this information is neatly encapsulated in ADO's online help. For each object type, search through online help and choose the object summary page for a complete list of the properties and methods each object exposes.
Connection Strings
The most important property of the Connection object is its ConnectionString property. The ConnectionString property contains all of the information that ADO uses to locate and configure the OLE DB provider that's being used to retrieve data. Here's a typical ConnectionString property for an Access database:
Provider=Microsoft.Jet.OLEDB.4.0;
User ID=Admin;
Data Source=C:\Sybex\Ch06.mdb;
Mode=Share Deny None;
Extended Properties="";
Locale Identifier=1033;
Persist Security Info=False;
Jet OLEDB:System database=C:\SYSTEM.MDW;
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=5;
Jet OLEDB:Database Locking Mode=1;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False
A connection string specifies three types of information:
- The name of the OLE DB provider to use (in this case, Microsoft.Jet.OLEDB.4.0, the version of the Jet provider that ships with Access 2000).
- Standard ADO Connection properties (in this case, all the lines from "User ID" to "Persist Security Info") that are applicable to all, or nearly all, OLE DB providers.
- Provider-specific Connection properties (in this case, all the lines that start with "Jet OLEDB") that are applicable only to the particular provider.
Tip
Almost anywhere that a Connection object is required in ADO, you can supply a ConnectionString instead to have the Connection object implicitly created. That is, generally, you needn't create an explicit Connection object. You can simply assign the ConnectionString property, which contains all the information ADO requires in order to create a Connection object for you.
If you're working within Access and are therefore reusing CurrentProject.Connection, you can safely ignore all the complexity of the connection string. If you're opening a connection to another database, though, or working in a non-Access VBA host, you may need to build your own connection strings. Fortunately, if you just want to retrieve data from an unsecured Access database, you can use a very simple connection string, as shown here (from basADO):
Public Sub Reconnect()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
CurrentProject.Path & "\ConnectToMe.MDB"
cnn.Open
Debug.Print cnn.ConnectionString
Set cnn = Nothing
End Sub
Tip
This example uses the CurrentProject.Path property to determine the path of the current database or project file. The example then uses this path to find another database, ConnectToMe.MDB, as the data source. You may want to use the current path or some other path as your own data source in a connection string.
This code creates an ADO Connection object, sets its ConnectionString property, and opens the connection. The code then verifies its success by printing the ConnectionString property to the Immediate window. As you can see, the only required portions of the ConnectionString property are the name of the OLE DB provider and the name of the database to open.
Tip
One of the great things about ADO is its flexibility. That is, you often have many choices you can make when writing code with ADO. For example, in the previous code fragment, we specified the ConnectionString property of the Connection object before calling its Open method. We could also have passed the same connection string as the first parameter of the Open method. As you read our code (and other developers' code), you'll see that everyone handles these issues a little differently, and often inconsistently, within an application. We've tried to mix and match here, so you'll see different ways to specify parameters, set properties, and call methods.
Inevitably, the time will come when you need to construct a more complex connection string. As you can see, the format of a connection string is simple: a set of property=value pairs, separated by semicolons. Table 6.1 shows the properties you're most likely to need to set in a connection string using the Jet OLE DB provider. The table indicates for each property whether it applies to all ADO connections or only to those made with the Jet OLE DB provider.
Note
For a complete list and discussion of the properties you can set in a connection string for the Jet Provider, see the white paper "ADO Provider Properties and Settings," which is included on the companion CD.
TABLE 6.1: Connection String Properties for the Jet OLE DB Provider
| PROPERTY NAME | TYPE | EXPLANATION |
|---|---|---|
| Jet OLEDB: Database Locking Mode | Jet | 0 for page-level locking, 1 for row-level locking. |
| Jet OLEDB: Database Password | Jet | Password to be used for a password-protected database. Don't confuse this with the user password for a secured database, which is set with the PASSWORD property. |
| Data Source | ADO | The full path and filename of the database to open. |
| Mode | ADO | One of the following intrinsic constants to indicate how the database should be opened: adModeRead (read-only), adModeWrite (write-only), adModeReadWrite (read/write), adModeShareDenyRead (prevents others from opening in read mode), adModeShareDenyWrite (prevents others from opening in write mode), or adModeShareDenyExclusive (prevents others from opening in any mode). |
| Password | ADO | Password to use when connecting to the data source. Don't confuse this with the password for a password-protected database, which is set with the Jet OLEDB:Database Password property. |
| Prompt | ADO | One of the following intrinsic constants that controls whether the user is prompted for connection information when the connection is initialized: adPromptAlways (always prompt, even if enough information is present), adPromptComplete (prompt only if more information, such as a password, is required to complete the connection), adPromptCompleteRequired (prompt for more information if necessary, but don't accept optional information), or adPromptNever (fail rather than prompting if not enough information is supplied in the connection string). |
| Jet OLEDB: System Database | Jet | Full path and filename of the system database to use when verifying user ID and password information. |
| User ID | ADO | User name to use when connecting to the data source. |
Data Links
Rather than using CurrentProject.Connection or entering your own connection information, ADO also supports storing connection information in an external file, called a data link file (which normally has a UDL file extension). Data link files provide two important capabilities:
- They implement a graphical interface for constructing OLE DB connection strings.
- They offer a way to allow users to edit connection information, for example, by entering their own user name and password, without the necessity of writing code to capture this information.
To create a new data link file, right-click in Windows Explorer and choose New | Microsoft Data Link. This will create a file named New Microsoft Data Link.UDL in the current folder. You can rename the file, but it's best to keep the UDL extension. After you've named the file, double-click it to edit the Data Link properties. Select the Provider tab in the Data Link Properties dialog and choose the Microsoft Jet 4.0 OLE DB Provider. Then select the Connection tab and fill in the database name and logon information, as shown in Figure 6.5. You can test the connection to verify that the information entered is correct, and then click OK to save the file.
Figure 6.5: Setting the Connection properties for a Jet data link file
If you'd like to edit any of the provider-specific properties for this connection, use the All tab of the Data Link properties dialog.
Once you've created a data link file, you needn't supply any information when opening the connection in code other than the location of the UDL file. This makes it simple for you to manage changing data sources without having to modify any code. When you want to open a connection based on a UDL file, you simply specify
FileName="YourFileName.UDL"
as the ConnectionString property, or specify this when you call the Open method. This code sample (from basDataLink) shows how you can use a saved data link file to open an ADO connection to a database:
Public Sub TestDataLink()
' To set things up, run the
' ShowDataLink procedure before
' running this one.
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "File Name=" & _
CurrentProject.Path & "\Ch06.UDL"
Debug.Print cnn.ConnectionString
Set cnn = Nothing
End Sub
WARNING
If you're using a UDL file to refer to an Access database, you'll need to specify the path to the database file in the UDL file. In the example shown here, you'll need to modify the Ch06.UDL file, and set the path to the ConnectToMe.MDB file to refer to the path in which you've installed this file. Otherwise, ADO looks in the current path for the MDB file.
Programming Data Links
You can also use the Data Link interface from code in your database. To do this, you need to set a reference to the Microsoft OLE DB Service Component 1.0 Type Library (use the Tools | References menu item to do this; it's already done for you in the sample database), which implements a DataLinks object, using the MSDASC type library. You can create a new DataLinks object, assign default properties to the connection, and then present those properties to the user to edit in the Data Link Properties dialog. Listing 6.1 shows code that does this, from basDataLink in CH06.MDB.
Tip
Don't look for online help for objects within the MSDASC type library. It's just not there. This type library ships without a help file.
Listing 6.1
Public Sub ShowDataLink()
Dim cnn As ADODB.Connection
Dim dlk As MSDASC.DataLinks
Set cnn = New ADODB.Connection
Set dlk = New MSDASC.DataLinks
' Set default properties for the connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Data Source") = _
CurrentProject.Path & "\ConnectToMe.MDB"
' Tell the Data Link Properties dialog which
' window will be its parent
dlk.Hwnd = Application.hWndAccessApp
' Prompt the user for information
If dlk.PromptEdit(cnn) Then
cnn.Open
End If
Set cnn = Nothing
Set dlk = Nothing
End Sub
Referring to Objects
Referring to objects within a hierarchy follows a specific pattern. You can see this most clearly when using the ADOX library, which has a deeper hierarchy than the ADO library. You refer to ADOX objects by following the hierarchy presented earlier in Figure 6.4. Start with a Catalog object and work your way down. The general format for referring to objects is
Catalog.ParentCollectionItem.ChildCollection("ChildObject")
where it may take several iterations through parent collections before you get to the child collection you're interested in.
Before you can use the Catalog object, you must instantiate it and connect it to the database you're interested in. Typically, you'll be working with the current user database. In this case, you can use the CurrentProject.Connection property that we've already discussed, as in this code fragment:
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Debug.Print cat.Tables(0).Name
To refer to any member of any collection, you can use one of four syntactical constructs. Table 6.2 lists the four available methods. (In each example, you're attempting to refer to the field named Street Number in a table named tblCustomers, starting with a Catalog variable named cat.)
TABLE 6.2: Methods for Referring to Objects
| SYNTAX | DETAILS | EXAMPLE |
|---|---|---|
| collection("name") | cat.Tables("tblCustomers").Fields("Street Number") | |
| collection(var) | Where var is a string or variant variable | strTable = "tblCustomers" strField = "Street Number" cat.Tables(strTable).Fields(strField) |
| collection(ordinal position) | Where ordinal position is the object's position within its collection | cat.Tables(0).Fields(0) |
| collection!name collection![name] | Brackets are necessary if name contains a nonstandard character, such as a space | cat.Tables("tblCustomers").Fields![Street Number] |
WARNING
Access and ADO number all built-in collections with ordinal values beginning with 0. Almost all other components in the Microsoft world number their collections starting with 1, and user-defined collections within Access are also numbered starting with 1. This is a point about which you'll want to be very careful.
Tip
ADOX objects provide information on the design of objects, but that's all they provide. That is, you won't be able to use ADOX to retrieve information on data stored within a table or retrieved by a query. Think of it this way: ADOX provides information on Access objects, and ADODB provides data. If you want to retrieve information about the controls on Access forms or reports, however, neither library provides that information. You'll need to open the object within Access and use Access' Forms, Reports, or DataAccessPages collection to retrieve information about an open form, report, or data access page.
All ADOX objects except the Catalog object have an associated collection that contains all the objects of the given type. For example, the Tables collection contains a Table object for each table saved in the database. Collections make it easy to "visit" all the objects of a specific type, looping through all the items in the collection. Because you can refer to all the items in a collection either by name or by position, you have the best of both worlds. If you know the specific object's name, you can find it by name, as in the following code fragment:
Debug.Print cat.Tables("tblCompanies").DateCreated
If you want to refer to an object by number, you can do that, too:
Debug.Print cat.Tables(0).DateCreated
If you're working with objects deep within a hierarchy, you can create references in one long string. For example, the following statement will retrieve the name of a column in an index in a table in the specified catalog:
Debug.Print cat.Tables("tblCustomers"). _
Indexes(0).Columns(0).Name
As this statement demonstrates, you're also free to mix references by name and by ordinal position within a single statement.
Bang (!) versus Dot (.) versus Quotes ("")
The bang ("!") and dot (".") identifier operators help describe the relationships among collections, objects, and properties in an expression. They indicate that one part of an expression belongs to another.
In general, you follow the bang with the name of something you created: a form, report, or control. The bang also indicates that the item to follow is an element of a collection. You'll usually follow the dot with a property, collection, or method name. Actually, under the covers, the bang separator really says, "retrieve the following object from the default collection of the parent object." (See the section "Using Default Collections" later in this chapter.) For example, when working with forms, you can refer to a control on a form like this:
Set ctl = Forms("frmTest").Controls("txtName")
or, because Controls is the default collection of a form, you can abbreviate that as either of the following:
Set ctl = Forms("frmTest")("txtName")
Set ctl = Forms("frmTest")!txtName
You can also think of the uses of these operators this way: a bang (or parentheses and quotes) separates an object from the collection it's in (a field in a table, a form in the Forms collection, a control on a form), while a dot separates an object from a property, method, or collection of that object.
If you refer back to Table 6.2, you'll see that there's always an alternative to using the bang operator: you can use the parentheses and quotes syntax. For example, these two statements refer to exactly the same property:
cat.Tables!tblCustomers.Columns!Address.Type
cat.Tables("tblCustomers").Columns("Address").Type
It turns out that, behind the scenes, the former style of dot-and-bang reference is translated to the latter style of parentheses-and-quotes reference when you execute such a statement. This means that, although using the bang operator will save you a bit of typing, you'll pay for it in a speed penalty. Our recommendation, and the style we've followed throughout this book, is to always use the parentheses and quotes format for referring to a member of a collection unless it's absolutely necessary to use the bang operator. In addition, if the object to which you're referring contains spaces (or other nonstandard characters), you'll have to treat these names specially when using the bang syntax—you'll need to surround the name in square brackets. If you use the parentheses/quotes syntax, all names are treated equally.
WARNING
One place where the bang operator is necessary is in query parameters that refer to form fields. That is, you cannot avoid the Forms!FormName!ControlName syntax in this case.
Tip
It's a hard habit to break—we've been using "!" since Access 1. But the fact is, except in a very few places (query parameters is the one that comes to mind) you needn't ever use a bang and should probably think about weaning yourself from this syntax if you currently use it. It's important that you understand what it's doing and what it means when you see it, but we suggest you not use it in your VBA code.
Finally, one more reason to use "("")" rather than "!" in your code: when you use parentheses/quotes, you're using a string expression to identify an object. If you're using a string expression, you can just as easily use a string variable. Using this technique makes it possible to identify the object you want to work with at runtime, using a variable. This can be a useful technique, and we'll employ it throughout this book.
Ordinal Positions
As you've seen, you can refer to an object using the ordinal position within its collection. The database engine assigns and maintains these ordinal positions, and they always start with position number 0. For example, if you open a Recordset variable and inspect its collection of Field objects, the first one will have the ordinal of zero.
An object's ordinal position is dependent on the order in which it was added to its collection. If you're creating a recordset by appending fields, the first field you append will have a lower ordinal than the later fields. As you create and delete objects, an object's ordinal position changes within its collection. For this reason, it is not a good idea to refer to a specific object using its ordinal position. You should use the ordinal position of objects only as loop indexes for iterating through all the objects in a collection.
Note
Using an object's ordinal position has become less important with the addition of the For Each…Next construct a few versions back. There are times, however, when you must loop through the entries in a collection. If your action changes the number of elements in the collection, using For Each…Next will, in general, fail. In cases when you're closing objects or deleting them from their collection, use a For…Next loop, using the objects' ordinal position to refer to them. Also, For Each…Next can only visit items in a collection from front to back. If you need to iterate through items in any other order (from back to front, for example), you'll need to use a normal For…Next loop instead.
Using Default Collections
You can see from previous examples that a simple object reference can result in a long line of code. Fortunately, ADO provides default collections for some object types: if you don't specify a collection, Access assumes you're referring to the default collection for the parent object. You can use the default collection behavior of objects to make your code more compact (but somewhat less readable). Table 6.3 lists the default collection within each object type.
TABLE 6.3: Default Collections for ADO and ADOX Objects
| OBJECT | DEFAULT COLLECTION |
|---|---|
| Connection | (none) |
| Command | Parameters |
| Recordset | Fields |
| Error | (none) |
| Parameter | (none) |
| Field | (none) |
| Property | (none) |
| Catalog | Tables |
| Table | Columns |
| Index | (none) |
| Key | (none) |
| Group | (none) |
| User | (none) |
| Procedure | (none) |
| View | (none) |
Using default collections, you can shorten this expression:
Recordset.Fields(0)
to
Recordset(0)
This expression means, "Refer to the first field within the recordset" because the default collection for the Recordset object is the Fields collection.
You can use similar contractions on both Fields and Parameters to simplify your code. Be aware, though, that using default collections to reduce your code also makes it less readable: whoever is reading the code will have to understand the meaning of the expression without any visual clues. In addition, it doesn't make your code run any faster—it's simply a way to make it easier for you to type the code.
Enumerating Objects in Collections
Because you can access any object in any collection by its position in the collection, you can use a loop to look at or modify any object in the collection. Use the Count property of a collection to determine the size of the collection. Remember that the ordinal position of objects within an ADO collection starts at 0; if a collection contains three elements, they'll be numbered 0 through 2.
For example, you could use code like this (from basADO) to print out the names of all the Fields in a Recordset:
Public Sub ListFields()
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim intI As Integer
Set rst = New ADODB.Recordset
rst.Open "tblCustomers", CurrentProject.Connection
For intI = 0 To rst.Fields.Count - 1
Set fld = rst.Fields(intI)
Debug.Print fld.Name
Next intI
rst.Close
Set fld = Nothing
Set rst = Nothing
End Sub
The simplest way to loop through any collection, however, is to use the For Each…Next syntax. This syntax requires you to create a variable that can refer to the object type in the collection you're looping through and then use code like this to do the work:
Public Sub ListFieldsForEach()
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set rst = New ADODB.Recordset
rst.Open "tblCustomers", CurrentProject.Connection
For Each fld In rst.Fields
Debug.Print fld.Name
Next fld
rst.Close
Set rst = Nothing
End Sub
In this case, For Each…Next does the "Set" operation for you. That is, it iterates through each item in the collection, uses an implicit Set to point to the object variable you've supplied at each object in the collection, in turn, and allows you to work with that object variable. When you ask it to move on, it resets the pointer to refer to the next object for you.
Working with Properties
If you have worked with forms, reports, and controls, you are already familiar with referencing properties. (See Chapters 7 through 9 for more information on user-interface objects.) However, the interaction between the Jet engine and Microsoft Access introduces new subtleties when you are working with properties.
Properties for ADO objects behave somewhat differently from Microsoft Access properties. As you saw earlier in this chapter, every object has a collec-tion of properties.
Types of Properties
ADO properties can be either built-in or dynamic.
Built-in properties always exist for an object. They define the basic characteristics of an object and are available to any application that uses the object via ADO or ADOX. For example, for Field objects Name and Type are built-in properties. They define the basic characteristics of a field. You can refer to these properties using the dot syntax, for example, fld.Name. However, these properties are not a part of the Properties collection of the object and cannot be retrieved from that collection. Attempting to do so will generate runtime error 3265, "ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application."
Dynamic properties are defined by the OLE DB provider that supplies the object. These properties do not necessarily exist for every instance of an object, though they will be the same for all objects of a specific type from a specific provider. You can refer to these properties using the Properties collection only. Attempting to use the dot syntax will result in uncompilable code.
Dynamic properties have four (and only four) properties of their own:
- Name, a string that identifies the property
- Type, an integer specifying the property data type
- Value, the current value of the property
- Attributes, a Long integer carrying provider-specific information
Listing 6.2 (from ShowProperties in basObjects) demonstrates the difference between built-in and dynamic properties.
Listing 6.2
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set rst = New ADODB.Recordset
rst.Open "tblCustomers", _
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Set fld = rst.Fields(0)
' Return the built-in name property
Debug.Print fld.Name
' Return the dynamic BASECOLUMNNAME property
Debug.Print fld.Properties("BASECOLUMNNAME")
' Attempt to retrieve a built-in property from
' the properties collection gives error 3265
Debug.Print fld.Properties("Name")
' BASECOLUMNNAME is a dynamic property. The
' next line won't even compile
' Debug.Print fld.BASECOLUMNAME
WARNING
The DAO library supported a third class of properties, user-defined properties, that could be added to the Properties collection of an object. These properties could be added either by Microsoft Access as a client of the Jet engine or by you as an application developer. ADO version 2.1, as used in Office 2000, does not support user-defined properties. If you have a need to use such properties, you'll need to use the DAO library. Although ADO doesn't directly support user-defined properties, Access supplies the AccessObjectProperties collection, to which you can add your own properties for forms, reports, or any other Access object. However, this subject is beyond the scope of this chapter; see online help for more information on using the AccessObjectProperties collections.
Enumerating the Properties
Listing 6.3 shows code you could use to print out all the dynamic properties of any table:
Listing 6.3
Public Sub ListProperties(strTable As String)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim prp As ADOX.Property
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection
' You could use the following expression:
' Set tbl = cat.Tables(strTable)
' but the Tables collection is the default
' collection for the Catalog object, so its use
' is unnecessary in the expression
Set tbl = cat(strTable)
For Each prp In tbl.Properties
Debug.Print prp.Name, prp.Value
Next prp
Set cat = Nothing
Set tbl = Nothing
End Sub
You'll find ListProperties in basProperties (in CH06.MDB). The output from the preceding code might look something like the output shown in Figure 6.6.
Figure 6.6: Sample property listing for tblCustomers
WARNING
Note that the built-in properties of the table, such as its Name and Type, do not appear in the enumeration. Unfortunately, ADO does not provide a way to enumerate built-in properties. If you need to see all of the properties of an ADO or ADOX object, you'll need to use a tool such as the Object Browser to see what the built-in properties are.
Data Definition Using ADOX
The previous sections used ADO to refer to existing objects and their properties. However, you can also programmatically create and manipulate objects. To do this, you'll need to set a reference to the Microsoft ADO Extensions for DDL and Security (ADOX). This library contains additional objects that work with the core ADO objects to let you manipulate the schema of your database.
Creating Objects
To create a new object, follow these four steps:
- Open a Catalog object corresponding to your database.
- Create the new object by instantiating an object variable to refer to it.
- Define the new object's characteristics by setting its properties.
- Append the object to its collection to make it a permanent part of your database.
In cases where the new object contains other objects (a table contains fields, for instance), you must create the main object, then create the subordinate objects, append them to the appropriate collection, and then append the main object to its collection. You can use this same technique when creating a new table, index, or relation programmatically. The following sections demonstrate how to create these complex objects.
Creating a New Table
The following example creates a new table called tblOrders and adds two fields to it. You'll find the complete function in basCreateTable:
Public Sub CreateOrdersTable()
Dim cat As ADOX.Catalog
Dim tblOrders As ADOX.Table
Dim col1 As ADOX.Column
Dim col2 As ADOX.Column
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
' Delete the table, if it exists.
On Error Resume Next
cat.Tables.Delete "tblOrders1"
Set tblOrders = New ADOX.Table
tblOrders.Name = "tblOrders1"
Set col1 = New ADOX.Column
col1.Name = "OrderID"
col1.Type = adInteger
Set col2 = New ADOX.Column
col2.Name = "CustomerName"
col2.Type = adVarWChar
col2.DefinedSize = 30
' Code continues...
At this point, the new table and its two fields exist only in memory. To make the new objects a permanent part of the database, you must use the Append method. If you do not append a new object to a collection, it will not be saved as an object in the database.
WARNING
Creating objects and giving them properties is not enough. You must take the step of appending them to the correct collection, or Access will never know of their existence. If your program exits before you've used the Append method to add your objects to a collection, they will be discarded.
Note
ADOX is meant to work with any database engine. As a consequence of this, it uses the object name "Column" for what Access generally calls a "Field," at least in reference to table design. In recordsets, however, they're still called Fields.
The next lines save the new objects to the database:
With tblOrders.Columns
.Append col1
.Append col2
End With
With cat.Tables
.Append tblOrders
.Refresh
End With
' Code continues...
Finally, you can refresh the TableDefs collection to ensure that the new objects are included in it. In a multiuser environment, the new table may not be immediately available to other users unless you refresh the collection. The following line refreshes the TableDefs collection:
cat.Tables.Refresh
Even using the Refresh method, Access won't update the database window itself until it must. It will only show the new table you've created once you move to a different collection and then back to the list of tables. To solve this problem, Access 97 added the RefreshDatabaseWindow method of the Application object. Adding this line of code will refresh the database window's display:
Application.RefreshDatabaseWindow
When you're creating new columns using ADOX, you need to know which ADOX data type to use for each field. Table 6.4 shows the mapping between the ADOX Column object's Type property and the data type you'll see if you inspect the resulting table in the Access table designer.
TABLE 6.4: ADOX Column Data Types
| ADOX TYPE | ACCESS DATA TYPE |
|---|---|
| adBinary | Binary |
| adBoolean | Boolean |
| adUnsignedTinyInt | Byte |
| adCurrency | Currency |
| adDate | Date |
| adNumeric | Decimal |
| adDouble | Double |
| adGUID | GUID |
| adSmallInt | Integer |
| adInteger | Long |
| adVarWChar | Text |
| adSingle | Single |
| adLongVarWChar | Memo |
ADOX will allow you to combine creating and appending objects into a single step. For example, you can call the Append method of the Table object to both create and append a column. Using this approach allows you to dispense with variables to refer to columns.
For example, you could rewrite the preceding function as shown in Listing 6.4.
Listing 6.4
Public Sub CreateOrdersTable2()
Dim cat As ADOX.Catalog
Dim tblOrders As ADOX.Table
Set tblOrders = New ADOX.Table
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
' Delete the table, if it exists.
On Error Resume Next
cat.Tables.Delete "tblOrders2"
' The table object is already created, so
' just assign its properties
tblOrders.Name = "tblOrders2"
' Create the columns by appending them
With tblOrders.Columns
.Append "OrderID", adInteger
.Append "CustomerName", adVarWChar, 30
End With
With cat.Tables
.Append tblOrders
.Refresh
End With
Application.RefreshDatabaseWindow
Set cat = Nothing
Set tblOrders = Nothing
End Sub
Creating an Index
As part of your applications, you may need to create an index programmatically. Follow these steps to create a new index:
- Create an Index object and set its Name property.
- Assign values to the new index's properties, as appropriate. All the properties are read/write for an index object that hasn't yet been appended to the Indexes collection but are read-only once that has occurred. The ones you'll most likely be interested in are the Name, PrimaryKey, Unique, and IndexNulls properties.
- Create a Column object for each field that makes up part of the index, and append each to the index's Columns collection. This collection of fields indicates to the index the fields for which it must maintain values in order to keep itself current.
- Use the Append method of the original Table object to append the index object to its Indexes collection.
Note
Because all the properties of an index object are read-only once the object has been appended to its collection, if you need to modify a property of an index once it's been created, you must delete the object and then create a new one. You should also investigate the SQL ALTER TABLE statement, covered in Chapter 3.
Tip
In Access, using ADOX, you can name your indexes any way you wish. If you have code you're using, however, that counts on your primary key being named PrimaryKey, you must ensure that your primary keys are named with the standard value, PrimaryKey. Otherwise, existing code might break.
The adhCreatePrimaryKey function in Listing 6.5 creates the primary key for any specified table. You pass to this function the name of the table, the name of the primary key, and one or more field names to use as part of the primary key. Along the way, adhCreatePrimaryKey calls the FindPrimaryKey function, which returns the name of the primary key if it exists or Null if it doesn't. If a primary key already exists, adhCreatePrimaryKey deletes the primary key so it can create a new one. We've also included a test procedure, TestCreatePK, to test the functionality. You'll find all these examples in the module basPK in CH06.MDB.
Listing 6.5
Public Function adhCreatePrimaryKey( _
strTableName As String, _
strKeyName As String, _
ParamArray varColumns() As Variant) _
As Boolean
Dim cat As ADOX.Catalog
Dim idx As ADOX.Index
Dim tbl As ADOX.Table
Dim varPK As Variant
Dim varIdx As Variant
Dim idxs As ADOX.Indexes
On Error GoTo HandleErrors
Set cat = New ADOX.Catalog
cat.ActiveConnection = _
CurrentProject.Connection
Set tbl = cat.Tables(strTableName)
Set idxs = tbl.Indexes
' Find out if the table currently has a primary key.
' If so, delete it now.
varPK = FindPrimaryKey(tbl)
If Not IsNull(varPK) Then
idxs.Delete varPK
End If
' Create the new index object
Set idx = New ADOX.Index
idx.Name = strKeyName
' Set up the new index as the primary key
' This will also set
' IndexNulls to adIndexNullsDisallow
idx.PrimaryKey = True
idx.Unique = True
' Now create the columns that make up the index,
' and append each to the collection of columns
For Each varIdx In varColumns
AddColumn idx, varIdx
Next varIdx
' Now append the index to the Table's
' indexes colletion
idxs.Append idx
adhCreatePrimaryKey = True
ExitHere:
Set cat = Nothing
Set idx = Nothing
Set tbl = Nothing
Set idxs = Nothing
Exit Function
HandleErrors:
MsgBox "Error: " & _
Err.Description & " (" & Err.Number & ")"
adhCreatePrimaryKey = False
Resume ExitHere
End Function
Private Function FindPrimaryKey( _
tbl As ADOX.Table) As Variant
' Given a particular table, find the primary
' key name, if it exists.
' Returns the name of the primary key's index, if
' it exists, or Null if there wasn't a primary key.
Dim idx As ADOX.Index
For Each idx In tbl.Indexes
If idx.PrimaryKey Then
FindPrimaryKey = idx.Name
Exit Function
End If
Next idx
FindPrimaryKey = Null
End Function
Private Function AddColumn( _
idx As ADOX.Index, _
varIdx As Variant) As Boolean
' Given an index object, and a column name, add
' the column to the index.
' Returns True on success, False otherwise.
Dim col As ADOX.Column
On Error GoTo HandleErrors
If Len(varIdx & "") > 0 Then
Set col = New ADOX.Column
col.Name = varIdx
idx.Columns.Append col
End If
AddColumn = True
ExitHere:
Set col = Nothing
Exit Function
HandleErrors:
AddColumn = False
Resume ExitHere
End Function
Public Sub TestCreatePK()
Debug.Print adhCreatePrimaryKey("tblCustomerItems", _
"PrimaryKey", "CustomerID", "ItemID")
End Sub
Tip
You may have never used the ParamArray modifier, as we did in adhCreatePrimaryKey. Using this modifier, callers of your procedure can pass as many parameters as they like in that position. On the receiving side (in your code), you see all the parameters in that "slot" as a single array. Using ParamArray has some limitations, so you'll want to check out this interesting VBA feature in the online help.
Although the method shown above works for any index, ADOX provides shortcuts for creating primary and foreign key fields. When you create an index and set its PrimaryKey property to True, you're automatically creating a new Key in the Table's Keys collection as well. Similarly, if you create a relationship in the Access user interface, the foreign key field will automatically be indexed and a Key will be added to the Keys collection behind the scenes. You can reverse this process if you like by creating Keys directly. If you follow this procedure, Access will automatically create the corresponding indexes for you.
The adhCreatePrimaryKey2 function in Listing 6.6 demonstrates this alternative.
Listing 6.6
Public Function adhCreatePrimaryKey2( _
strTableName As String, _
strKeyName As String, _
ParamArray varColumns() As Variant) _
As Boolean
Dim cat As ADOX.Catalog
Dim pk As ADOX.key
Dim tbl As ADOX.Table
Dim varPK As Variant
Dim varIdx As Variant
Dim keys As ADOX.keys
On Error GoTo HandleErrors
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTableName)
Set keys = tbl.keys
' Find out if the table currently has a primary key.
' If so, delete it now.
varPK = FindPrimaryKey2(tbl)
If Not IsNull(varPK) Then
keys.Delete varPK
End If
' Create the new key object
Set pk = New ADOX.key
pk.Name = strKeyName
' Set up the new key as the primary key
pk.Type = adKeyPrimary
' Now create the columns that make up the key,
' and append each to the collection of columns
For Each varIdx In varColumns
AddColumn2 pk, varIdx
Next varIdx
' Now append the key to the Table's
' keys colletion
keys.Append pk
adhCreatePrimaryKey2 = True
ExitHere:
Set cat = Nothing
Set pk = Nothing
Set tbl = Nothing
Exit Function
HandleErrors:
MsgBox "Error: " & _
Err.Description & " (" & Err.Number & ")"
adhCreatePrimaryKey2 = False
Resume ExitHere
End Function
Private Function FindPrimaryKey2( _
tbl As ADOX.Table) As Variant
' Given a particular table, find the primary
' key name, if it exists.
' Returns the name of the primary key, if
' it exists, or Null if there wasn't a primary key.
Dim key As ADOX.key
For Each key In tbl.keys
If key.Type = adKeyPrimary Then
FindPrimaryKey2 = key.Name
Exit Function
End If
Next key
FindPrimaryKey2 = Null
End Function
Private Function AddColumn2( _
key As ADOX.key, _
varIdx As Variant) As Boolean
' Given a key object, and a column name, add
' the column to the key.
' Returns True on success, False otherwise.
Dim col As ADOX.Column
On Error GoTo HandleErrors
If Len(varIdx & "") > 0 Then
Set col = New ADOX.Column
col.Name = varIdx
key.Columns.Append col
End If
AddColumn2 = True
ExitHere:
Set col = Nothing
Exit Function
HandleErrors:
AddColumn2 = False
Resume ExitHere
End Function
Public Sub TestCreatePK2()
Debug.Print adhCreatePrimaryKey2("tblCustomerItems", _
"PrimaryKey", "CustomerID", "ItemID")
End Sub
Creating Relationships
Follow these steps to create a new relationship:
- Open the catalog that will be the basis for your relation.
- Verify that the referenced table (the primary table in the relation) has a primary key in place.
- Create a Key object to represent the foreign key (the many side of the relation). Append columns to this key.
- For each column object in the foreign key, supply the RelatedColumn property, which corresponds to the name of the matching key field in the primary table.
- Use the Append method to append the new key object to the table's keys collection.
To specify cascading updates or cascading deletes, you set properties of the new foreign key object. To enforce cascading updates, set the key's UpdateRule property to the constant adRICascade. To enforce cascading deletes, set the key's DeleteRule property to the constant adRICascade, as well.
Listing 6.7 demonstrates, in the simplest case, the steps involved in creating a relationship. This function (from basRelations in CH06.MDB) creates a one-to-many relationship between tblCustomers and tblCustomerItems and enables cascading updates. Just to make sure the function succeeds, if it finds that the relation already exists, it deletes that relation and recreates it.
Listing 6.7
Const adhcErrObjectExists = -2147467259
Public Function CreateRelationship() As Boolean
' Create a relationship between tblCustomers
' and tblCustomerItems.
' The relation will have cascading deletes
' enabled.
Dim cat As ADOX.Catalog
Dim fk As ADOX.key
Dim tbl As ADOX.Table
Dim keys As ADOX.keys
On Error GoTo HandleErrors
' Get the catalog for the current database
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
' Get the keys collection for the many-side table
Set tbl = cat.Tables("tblCustomerItems")
Set keys = tbl.keys
' Create the foreign key
Set fk = New ADOX.key
fk.Name = "CustomerItemsCustomers"
fk.Type = adKeyForeign
fk.RelatedTable = "tblCustomers"
' Specify cascading deletes
fk.DeleteRule = adRICascade
' Append a column to the key
fk.Columns.Append "CustomerID"
' Set the related column name
fk.Columns("CustomerID").RelatedColumn = _
"CustomerID"
' Append the key object to save it
tbl.keys.Append fk
CreateRelationship = True
ExitHere:
Set fk = Nothing
Set keys = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
HandleErrors:
Select Case Err.Number
Case adhcErrObjectExists
' If the relationship exists,
' just delete it and try again
tbl.keys.Delete fk.Name
Resume
Case Else
MsgBox "Error: " & Err.Description & _
" (" & Err.Number & ")"
CreateRelationship = False
Resume ExitHere
End Select
End Function
Working with Recordsets
In almost any Access application, sooner or later you'll need to manipulate data from within VBA. ADO provides a rich set of data access objects to allow you to view, edit, add, and delete fields, rows, and tables. In addition, ADO allows you to specify a number of properties that control the behavior of recordsets, including cursor type, lock type, cursor location, and recordset options. The following sections discuss these issues.
Note
ADO uses the term cursor to refer to the behavior of the record pointer that specifies the current row in a recordset.
Meet the Cursors
A cursor, in ADO, is the underlying object that makes it possible for you to move around within the set of rows returned by a recordset. The cursor manages movement, updatability, and currency of the rows you've requested. In DAO, you'll find three simple cursor types: table, dynaset, and snapshot. ADO's cursor options are a bit more flexible and are perhaps more complex.
Although ADO provides four types of recordset cursors, the one you use in any given situation depends on the source of the data being referenced and the methods you need to use to access the data. Table 6.5 lists each cursor type, along with its benefits and drawbacks.
TABLE 6.5: Cursor Types and Their Benefits/Drawbacks
| CURSOR TYPE | DESCRIPTION |
BENEFITS |
DRAWBACKS |
|---|---|---|---|
| Dynamic | Set of records in a table or other data source in a database | Shows all record changes by other users. | Allows bookmarks only if the provider supports them. Not supported by the Jet OLE DB provider in this manner. Jet uses this cursor type to provide best performance for cursors based on SQL strings. |
| Keyset | Set of pointers (bookmarks) referring to data in tables or queries in a database | Shows data changes by other users. Always supports bookmarks. | Does not show new records added by other users. Prevents access to records deleted by other users. |
| Static | Copy of a set of records as it exists at the time the recordset is created | Always supports bookmarks. The only type of recordset allowed by client-side cursors. | Doesn't reflect changes to data made in a multiuser environment. |
| Forward-only | Copy of a set of records as it exists at the time the recordset is created | Faster than a static cursor. | Only allows scrolling forward through records. |
WARNING
Don't be fooled by the name "static." Static recordsets contain a fixed set of records, but these records may or may not be editable depending on the lock type of the recordset.
Creating a Recordset
You use code like the following to create a recordset:
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open Source, Connection, CursorType, LockType, Options
All of the parameters are optional. Additionally, ADO lets you specify parameters before you open the recordset. Therefore, either of these code snippets has the same effect:
rst.Connection = CurrentProject.Connection
rst.CursorType = adopenStatic
rst.LockType = adLockOptimistic
rst.Open "Customers"
or
rst.open "Customers", CurrentProject.Connection, _
adOpenStatic, adLockOptimistic
Note
Throughout this chapter, and throughout the book, we'll interchange the use of the different ways you can open a recordset. Sometimes, we'll set the properties first and then call the Open method. Other times, we'll pass all the values in the Open method call itself. The point here is that it doesn't matter which way you do it—it's up to you. In addition, we'll often use named parameters; that is, supplying a ParameterName:=Value pair for parameters. This makes your code easier to read, we think, when you have multiple parameters to send to a procedure.
The Source parameter indicates where the data will come from and must be one of the following:
- Name of an existing table
- Name of an existing query or stored procedure that returns rows
- A SQL statement that returns rows
- An existing Command object variable name
- The file name of a persisted recordset
The CursorType parameter specifies the type of cursor that ADO will use to move through the recordset. It should be one of the following built-in constant values:
- adOpenDynamic, to open a dynamic recordset
- adOpenKeyset, to open a keyset recordset
- adOpenStatic, to open a static recordset
- adOpenForwardOnly, to open a forward-only recordset
If you don't specify a cursor type, ADO defaults to the fastest type, which is a forward-only recordset.
The LockType parameter specifies the record-locking behavior that will be used for editing operations in this recordset. It should be one of the following built-in constant values:
- adLockReadOnly, for recordsets that cannot be edited
- adLockPessimistic, for recordsets that use pessimistic locking
- adLockOptimistic, for recordsets that use optimistic locking
- adLockBatchOptimistic, for recordsets that will use the UpdateBatch method to update multiple records in a single operation
If you don't specify a lock type, ADO defaults to the fastest type, which is a read-only recordset.
WARNING
The default recordset in ADO is forward-only and read-only. This means that if you simply open a recordset, you won't be able to do much with it. If you want to move through records at random or edit records, you must specify the cursor type and lock type to use!
The Options parameter tells ADO how to evaluate the Source parameter. It can be one of the values listed in Table 6.6.
TABLE 6.6: Options for Recordsets
| CONSTANT | DESCRIPTION |
|---|---|
| adCmdText | Tells the provider to evaluate the source as a SQL statement. |
| adCmdTable | Tells ADO to generate a SQL query to retrieve all rows from the table or query whose name is specified as the source. |
| adCmdTableDirect | Tells the provider to return all records from the table or query whose name is specified as the source. |
| adCmdStoredProc | Tells the provider to evaluate the source as the name of a stored procedure or query. |
| adCmdUnknown | Tells ADO to query the provider to determine whether the source is a stored procedure, table name, or SQL statement. |
| adCmdFile | Tells ADO that the source parameter is the name of a file containing a persisted recordset. |
If you don't specify any options, ADO defaults to adCmdUnknown. This will cause slightly slower performance, since ADO needs to query the system tables from the OLE DB provider to determine what type of object the recordset is to be based on.
Note
You can also open a recordset by calling the Execute method of a Command object or a Connection object. However, a recordset opened with the Execute method is always forward-only, read-only. For flexibility, you'll want to stick to the Open method of the Recordset object.
Direct Table Access
By default, recordsets are based on SQL statements that retrieve rows from the specified data source. Even if you specify the name of a table as the Source parameter to the recordset's Open method, ADO will translate this into the SQL statement
SELECT * FROM Source
and send that statement to the OLE DB provider to be evaluated.
Using SQL statements to retrieve records allows ADO to work with the most general OLE DB providers. However, with some providers (including the Jet and SQL Server OLE DB providers), you can bypass building a SQL statement when your data source is an actual table in the database. To do this, you specify adCmdTableDirect as the Options parameter to the Open method of the recordset.
If you want your code to be as general as possible for use against any OLE DB provider, you should avoid the adCmdTableDirect option. However, some operations (such as using the Seek method to locate a record using an index) can only be performed if you use adCmdTableDirect.
Consistent versus Inconsistent Updates
When you create a recordset object based on more than one table, Jet allows you to make changes only to the "many" side of a join, by default. This is known as a consistent update. At times, you may want to update both sides of a join. To do this, you need to tell ADO that you wish to make inconsistent updates. This allows you to update fields in both sides of the join. Note that this may violate the relationships between tables that your application needs. It is up to you to provide the necessary code to ensure that any "implied" referential integrity is maintained.
If you've turned on referential integrity for a relationship and enabled cascading updates, the inconsistent and consistent updates will cause identical behavior. In this case, the referential integrity takes control, and the cascading updates will update the many side of the relationship when you update the "one" side.
Most OLE DB providers do not support inconsistent updates, so this is not something that's part of the default ADO object model. Jet, however, does support this behavior. When you open a recordset using the Jet provider, the provider adds a dynamic property to the recordset object that lets you use inconsistent updates if you choose. To do so, you must first set the connection for the recordset, then set the value of the property, and then open the recordset, as shown in Listing 6.8 (from basRecordset).
Listing 6.8
Public Sub OpenInconsistent()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Properties("Jet OLEDB:Inconsistent") = True
rst.Open _
Source:="SELECT * FROM tblCustomers" & _
" INNER JOIN tblOrders " & _
" ON tblCustomers.CustomerID = tblOrders.CustomerID", _
Options:=adCmdText
Debug.Print rst.RecordCount
Set rst = Nothing
End Sub
Cursor Locations
If you've worked with DAO previously, you know that you never have to be concerned with where Jet places the cursor. As a matter of fact, this isn't an issue that even comes up using DAO. A cursor is the cached rows, or row pointers, provided by the database engine when you open a recordset. ADO supports two cursor locations: client-side and server-side. A client-side cursor is created on the same machine as the client (that's you), and a server-side cursor is created on the same machine as the server (that might be on your machine, or it might be on some server machine. If you're using Jet, this is always your machine, because Jet always runs on your machine.) To choose between the two, you set the CursorLocation property of the Recordset object to one of two intrinsic constants before opening the recordset:
- adUseServer (the default) specifies that ADO should use server-side cursors
- adUseClient specifies that ADO should use client-side cursors
When you specify client-side cursors, ADO passes your request to the Microsoft Cursor Service for OLE DB. This component is designed to enhance the functionality of recordsets retrieved from any OLE DB provider. The Cursor Service first requests all the data from the OLE DB provider using a forward-only, read-only recordset. Then it caches this data locally and presents it to the application as a static recordset.
In most cases, using client-side cursors with an Access database only adds overhead to data access, because data is being cached twice on the same machine: once in the Jet database engine and once in the Cursor Service. However, you must use client-side cursors for some advanced functionality. In particular, client-side cursors are necessary if you want to call the Sort method on a recordset, or if you want to create a disconnected recordset that will later reconnect to the database and update multiple records in a batch.
In general, you should use the default server-side cursors unless you discover that the additional functionality of client-side cursors is necessary for your application.
Tip
When using Jet to provide your data (as opposed to using SQL Server/MSDE or some other OLE DB provider), you'll want to use a server-side cursor whenever possible. You'll get better performance, and you won't have an extra layer of ADO (the client-side cursor is provided by ADO) between you and Jet. On the other hand, there are some things you simply cannot do with a server-side cursor. The Sort method, for example, only works with client-side cursors. Still, whenever possible, use the default, server-side cursor when working with Jet data.
Recordset Property Interactions
With two cursor locations, four cursor types, and four lock types, there are theoretically 32 variations of recordsets (not counting the fine details such as direct table access or inconsistent updates). ADO is designed to be very forgiving on this count. You can request any combination of location, cursor type, and lock type, and ADO will open a recordset without error. However, it may not open precisely what you request. For combinations that aren't supported, ADO will change one or more options to get a recordset type that it's able to deliver.
Table 6.7 shows the mapping between what you ask for and what you get in server-side recordsets. Table 6.8 shows the same mapping for client-side recordsets. If you'd like to investigate this behavior yourself, the tables were generated from information gathered by the SeeCursorInfo function in basRecordset (see Listing 6.9).
TABLE 6.7: Server-Side Recordset Properties
| REQUESTED | DELIVERED |
|---|---|
| Forward-only, read-only | Forward-only, read-only |
| Forward-only, pessimistic | Keyset, batch optimistic |
| Forward-only, optimistic | Keyset, optimistic |
| Forward-only, batch optimistic | Keyset, batch optimistic |
| Keyset, read-only | Keyset, read-only |
| Keyset, pessimistic | Keyset, batch optimistic |
| Keyset, optimistic | Keyset, optimistic |
| Keyset, batch optimistic | Keyset, batch optimistic |
| Dynamic, read-only | Static, read-only |
| Dynamic, pessimistic | Keyset, batch optimistic |
| Dynamic, optimistic | Keyset, optimistic |
| Dynamic, batch optimistic | Keyset, batch optimistic |
| Static, read-only | Static, read-only |
| Static, pessimistic | Keyset, batch optimistic |
| Static, optimistic | Keyset, optimistic |
| Static, batch optimistic | Keyset, batch optimistic |
TABLE 6.8: Client-Side Recordset Properties
| REQUESTED | DELIVERED |
|---|---|
| Forward-only, read-only | Static, read-only |
| Forward-only, pessimistic | Static, batch optimistic |
| Forward-only, optimistic | Static, optimistic |
| Forward-only, batch optimistic | Static, batch optimistic |
| Keyset, read-only | Static, read-only |
| Keyset, pessimistic | Static, batch optimistic |
| Keyset, optimistic | Static, optimistic |
| Keyset, batch optimistic | Static, batch optimistic |
| Dynamic, read-only | Static, read-only |
| Dynamic, pessimistic | Static, batch optimistic |
| Dynamic, optimistic | Static, optimistic |
| Dynamic, batch optimistic | Static, batch optimistic |
| Static, read-only | Static, read-only |
| Static, pessimistic | Static, batch optimistic |
| Static, optimistic | Static, optimistic |
| Static, batch optimistic | Static, batch optimistic |
Note that these tables only apply to recordsets opened on native Jet data sources. The recordsets delivered by ADO depend both on the capabilities of ADO itself and the capabilities of the underlying OLE DB provider. So, for example, although Jet doesn't ever deliver Dynamic recordsets, some other data source used from an Access database (such as a SQL Server table) might.
Listing 6.9
Public Sub SeeCursorInfo()
Dim rst As ADODB.Recordset
Dim lngLocation As Long
Dim lngCursorType As Long
Dim lngLockType As Long
On Error GoTo HandleErr
Set rst = New ADODB.Recordset
For lngLocation = 2 To 3
For lngCursorType = 0 To 3
For lngLockType = 1 To 4
Debug.Print "Requesting " & _
GetLocation(lngLocation) & ", " & _
GetCursorType(lngCursorType) & ", " & _
GetLockType(lngLockType)
rst.CursorLocation = lngLocation
Set rst.ActiveConnection = _
CurrentProject.Connection
rst.Open "tblCustomers", _
CursorType:=lngCursorType, _
LockType:=lngLockType
Debug.Print " Got " & _
GetLocation(rst.CursorLocation) & ", " & _
GetCursorType(rst.CursorType) & ", " & _
GetLockType(rst.LockType)
NextType:
If Not rst Is Nothing Then
rst.Close
End If
Next lngLockType
Next lngCursorType
Next lngLocation
ExitHere:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub
HandleErr:
Debug.Print " Error : " & _
Err.Description & " (" & Err.Number & ")"
Resume NextType
End Sub
Private Function GetLocation( _
lngLocation As ADODB.CursorLocationEnum) As String
Select Case lngLocation
Case adUseServer
GetLocation = "Server side"
Case adUseClient
GetLocation = "Client side"
End Select
End Function
Private Function GetLockType( _
lngLockType As ADODB.LockTypeEnum) As String
Select Case lngLockType
Case adLockReadOnly
GetLockType = "Read only"
Case adLockPessimistic
GetLockType = "Pessimistic"
Case adLockOptimistic
GetLockType = "Optimistic"
Case adLockBatchOptimistic
GetLockType = "Batch optimistic"
End Select
End Function
Private Function GetCursorType( _
lngCursorType As ADODB.CursorTypeEnum) As String
Select Case lngCursorType
Case adOpenForwardOnly
GetCursorType = "Forward-only"
Case adOpenKeyset
GetCursorType = "Keyset"
Case adOpenDynamic
GetCursorType = "Dynamic"
Case adOpenStatic
GetCursorType = "Static"
End Select
End Function
The Supports Method
As you'll see throughout this chapter, not all recordsets are created equal. When you take into account the different ways that you can open recordsets; the various permutations of the CursorLocation, CursorType, LockType, and Options properties; and the different potential OLE DB providers that can be supplying the data, it can be difficult to be sure just which methods will work on which recordsets. If you're trying to write general-purpose code to manipulate any recordset, this can be a real problem. Fortunately, ADO provides the Supports method, which allows you to query a recordset as to the functionality that it supports.
The Supports method returns True or False for specific options:
fReturn = rst.Supports(Option)
Where Option is one of the intrinsic constants shown in Table 6.9.
TABLE 6.9: Constants for the Supports Method
| OPTION | RETURNS TRUE IF... |
|---|---|
| adAddNew | You can use the AddNew method to add records to this recordset. |
| adApproxPosition | You can use the AbsolutePosition and AbsolutePage properties with this recordset. |
| adBookmark | You can use the Bookmark property with this recordset. |
| adDelete | You can use the Delete method to delete records from this recordset. |
| adFind | You can use the Find method to find records in this recordset. |
| adHoldRecords | You can change the recordset position without committing changes to the current record. |
| adIndex | You can use the Index property to set an index for this recordset. |
| adMovePrevious | You can use MoveFirst and MovePrevious or the Move method to move backwards in this recordset. |
| adResync | You can use the Resync method to resynchronize this recordset with the underlying data. |
| adSeek | You can use the Seek method to find records in this recordset. |
| adUpdate | You can use the Update method to modify records in this recordset. |
| adUpdateBatch | You can use the UpdateBatch and CancelBatch methods on this recordset. |
Listing 6.10 shows how you can use the Supports method in your code to check the functionality of a recordset. You'll find both the RecordsetSupport function and the TestRecordsets function in basRecordset.
Listing 6.10
Private Sub RecordsetSupport(rst As ADODB.Recordset)
If rst.Supports(adAddNew) Then
Debug.Print "Supports AddNew"
Else
Debug.Print "Doesn't support AddNew"
End If
If rst.Supports(adApproxPosition) Then
Debug.Print "Supports AbsolutePosition"
Else
Debug.Print "Doesn't support AbsolutePosition"
End If
If rst.Supports(adBookmark) Then
Debug.Print "Supports bookmarks"
Else
Debug.Print "Doesn't support bookmarks"
End If
If rst.Supports(adDelete) Then
Debug.Print "Supports Delete"
Else
Debug.Print "Doesn't support Delete"
End If
If rst.Supports(adFind) Then
Debug.Print "Supports Find"
Else
Debug.Print "Doesn't support Find"
End If
If rst.Supports(adHoldRecords) Then
Debug.Print "Supports move without save"
Else
Debug.Print "Doesn't support move without save"
End If
If rst.Supports(adIndex) Then
Debug.Print "Supports Index"
Else
Debug.Print "Doesn't support Index"
End If
If rst.Supports(adMovePrevious) Then
Debug.Print "Supports MovePrevious"
Else
Debug.Print "Doesn't support MovePrevious"
End If
If rst.Supports(adResync) Then
Debug.Print "Supports Resync"
Else
Debug.Print "Doesn't support Resync"
End If
If rst.Supports(adSeek) Then
Debug.Print "Supports Seek"
Else
Debug.Print "Doesn't support Seek"
End If
If rst.Supports(adUpdate) Then
Debug.Print "Supports Update"
Else
Debug.Print "Doesn't support Update"
End If
If rst.Supports(adUpdateBatch) Then
Debug.Print "Supports UpdateBatch"
Else
Debug.Print "Doesn't support UpdateBatch"
End If
End Sub
Public Sub TestRecordsets()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblCustomers", CurrentProject.Connection
Debug.Print "Default recordset:"
Debug.Print "=================="
RecordsetSupport rst
rst.Close
Debug.Print
rst.Open "tblCustomers", _
CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect
Debug.Print "Direct table recordset:"
Debug.Print "======================="
RecordsetSupport rst
rst.Close
rst.CursorLocation = adUseClient
Debug.Print
rst.Open "tblCustomers", _
CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic
Debug.Print "Client-side recordset:"
Debug.Print "======================"
RecordsetSupport rst
rst.Close
Set rst = Nothing
End Sub
Creating Recordset Objects
One of the wonderful things about ADO is that it's so flexible. This flexibility is also the curse of ADO, because there are so many ways you can write the same code, and no two programmers' code will look the same. For instance, the following examples show a number of ways you can create Recordset objects. This list isn't exhaustive, but it does show some representative cases.
To create a recordset based on a table or a saved query:
' This will create a forward-only, ' read only recordset Set rst = New ADODB.Recordset rst.Open "Customers", CurrentProject.Connection ' Another forward-only, read only ' recordset rst.Open "qryCustOrders", CurrentProject.ConnectionTo create an editable recordset that supports full navigation and sorting based on a SQL string:
' Client side recordset that can be sorted rst.CursorLocation = adUseClient rst.Open "SELECT * FROM Customers", _ CurrentProject.Connection, _ adOpenKeyset, adLockOptimisticTo create a recordset that supports using the Seek method:
' This recordset supports Seek rst.CursorLocation = adUseServer rst.Open "Customers", _ CurrentProject.Connection, adOpenKeyset, _ adLockOptimistic, adCmdTableDirect
Tip
Not all recordsets are created equally. To find out what features any given recordset supports, you can use the Supports method of the Recordset object. See the section "The Supports Method" earlier in this chapter for more information.
Moving Through a Recordset
Once you've created a recordset, ADO provides a variety of methods for navigating through the rows: MoveFirst, MoveLast, MovePrevious, and MoveNext. Each of these works in the manner you would expect based on the name. In addition, ADO provides the Move method, which can move a specified number of rows forward or backward, either from the current row or from a stored bookmark (if the recordset in question supports bookmarks). Recordsets also support the AbsolutePosition property, which allows you to read and write the current position within the recordset, based on the data in the current set of rows. The AbsolutePosition property returns a number from 1 to the total number of records in the recordset, or one of three intrinsic constants:
- adPosUnknown, if the recordset is empty or the provider doesn't support the property
- adPosBOF, if the BOF property is true (the cursor is before the first record in the recordset)
- adPosEOF, if the EOF property is true (the cursor is beyond the final record in the recordset)
The procedure in Listing 6.11, from basRecordset, demonstrates how the AbsolutePosition property works.
Listing 6.11
Public Sub ShowAbsolutePosition()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblCustomers", _
CurrentProject.Connection, adOpenKeyset
Debug.Print rst.Fields(0).Value
' Move approximately halfway
rst.AbsolutePosition = 0.5 * rst.RecordCount
Debug.Print rst.Fields(0).Value
' Move to the 35th row
rst.AbsolutePosition = 35
Debug.Print rst.Fields(0).Value
rst.Close
Set rst = Nothing
End Sub
Using the Move Method
Although the actions of the other Move methods are obvious, based on their names, the Move method is a bit more ambiguous. The Move method of a recordset accepts one or two parameters:
rst.Move numrecords [,start]
The numrecords parameter indicates the number of rows to move (greater than 0 for forward, less than 0 for negative), and the optional start parameter can contain a saved bookmark. If you supply the value for the bookmark, ADO starts there and moves the appropriate number of rows from that spot. If you don't specify the start location, ADO assumes you want to start moving from the current row. See the section "Adding New Rows to a Recordset" later in this chapter for an example of using the Move method.
Tip
See the section "Using Bookmarks" later in the chapter for more information on retrieving a bookmark and saving its value.
The Move method won't generate an error even if you try to move by too many records (for example, moving forward 100 records in a recordset containing only 50 records). In such a case, the record pointer stops at BOF or EOF, depending on the direction of the move.
Note
When working with a forward-only recordset, the rows parameter can be either positive or negative. However, an error results if you attempt to move backwards before the first record in the current record cache. You can set the number of records in the cache with the CacheSize property.
Using the AbsolutePosition Property
You can set the value of the AbsolutePosition property to move to a specific row in the recordset. If you wanted to move to the row approximately 50 percent of the way through your rows, you could use code like this:
rst.PercentPosition = .5 * rst.RecordCount
To move to the 35th row within the rows currently in the recordset, given the current filtering and sorting, you could use code like this:
rst.AbsolutePosition = 35
You can also use this property to find out where you are in the recordset—that is, to find out the current position, by number.
WARNING
The AbsolutePosition property is not a record number and should not be thought of as such. It simply returns the current row's position within the current set of rows, and it will change as you modify the filter or the sort order of the rows. To be able to find a row, no matter how you've modified the sorting or filtering, you'll need to use a bookmark (see the section "Using Bookmarks" later in this chapter) or store the primary key for later retrieval.
Finding the Number of Rows in a Recordset
You use the RecordCount property of a recordset to find the number of rows contained in the recordset. If ADO can't determine the number of rows, or if the type of cursor you've chosen (forward-only, for example) doesn't support finding the number of rows, the property returns –1.
In a single-user environment, the RecordCount property always correctly returns the number of rows in the recordset. If you delete a row, either interactively or programmatically, the RecordCount property stays in sync. In a multiuser environment, things are a bit more complex. If you're sharing data with another user and you both have a recordset open that's based on the same data, the issues are more complex. See Chapter 2 in Access 2000 Developer's Handbook, Volume II for more information on sharing data with multiple users.
Tip
If a recordset supports approximate positioning or bookmarks (see the section "The Supports Method," earlier in the chapter, to see how to find out if the recordset supports these features), ADO can retrieve the RecordCount property with minimal overhead. On the other hand, if the recordset doesn't support approximate positioning, ADO must actually populate the recordset to find out the record count, and this can be an expensive proposition—that is, it might cause a significant delay in your application. Be wary of using the RecordCount property, unless you've used the Supports method to verify that doing so won't take a long time.
Testing for Boundaries
Every recordset supports two properties, BOF and EOF, that indicate whether the current row is currently at the end of the recordset (EOF) or at the beginning of the recordset (BOF).
- If you use MovePrevious while the first row is current, BOF becomes True and there is no current row.
- If you use MovePrevious again, BOF stays True but a runtime error (3021) occurs.
- If you use MoveNext while the last row is current, EOF becomes True and there is no current row.
- If you use MoveNext again, EOF stays True but a runtime error (3021) occurs.
Testing for an Empty Recordset
Often when you create a recordset, you want to know immediately whether that recordset actually contains any rows. It's quite possible to create a recordset that doesn't return any rows, and you might need to take different steps based on whether the result contained any rows.
You can test for an empty recordset in a number of ways, but the two methods that follow ought to serve your needs. The following expression:
rst.Open "qryCust", CurrentProject.Connection
If Not rst.BOF And Not rst.EOF Then
' You'll only be in here if there are some rows.
End If
checks to see whether both the BOF and the EOF properties for the recordset are True. If so, there must not be any rows because that's the only way the current position could be at both the beginning and the end of the recordset. In addition, you often will want to loop through the rows of your recordset. In that case, you needn't check; just write the loop so that it won't start if there are no rows:
rst.Open "qryCust", CurrentProject.Connection
Do Until rst.EOF
' Process rows in here
Loop
You can also check the RecordCount property of a recordset: if it's 0, you know there aren't any records in the recordset. For example, you might use code like this:
rst.Open "qryCust", CurrentProject.Connection
If rst.RecordCount > 0 Then
' You'll only be in here if there are some rows.
End If
You may find this technique easier to use. However, this technique will not work on all recordsets, due to the behavior of the RecordCount property. For example, it will fail on forward-only recordsets, where the RecordCount is –1. We recommend that you use the first technique, rather than relying on the RecordCount property.
Looping through All the Rows
Although you're likely to have less reason than you'd think to loop through all the rows of a recordset (that's what action queries are for), the syntax is quite simple. The code in Listing 6.12 walks through a recordset backwards, from the end to the beginning and, if there are any records to be had, prints out one of the fields in the underlying data. (Look for ListNames in basRecordset.)
Listing 6.12
Public Sub ListNames()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.Open "tblCustomers", Options:=adCmdTableDirect
If .RecordCount > 0 Then
' Move to the end
.MoveLast
' Loop back towards the beginning
Do Until .BOF
Debug.Print .Fields("ContactName")
.MovePrevious
Loop
End If
.Close
End With
Set rst = Nothing
End Sub
Using Arrays to Hold Recordset Data
You can use the GetRows method of any recordset to copy its data into a Variant variable. Access will create a two-dimensional array with enough space to hold the data:
varData = rst.GetRows(intRowsToGrab, varStart, astrFields)
You don't have to set the size of the array; Access will do that for you. Because arrays give you random access to any row or column within the array, you may find it more convenient to work with arrays than with the recordset itself. For example, if you want the fastest access to data that you don't need to write to, you might want to use a forward-only recordset. But using this type of recordset limits your movement in the data. If you create a forward-only recordset and copy its data to an array, you've got the best of both worlds: fast access and random access.
The GetRows method takes three parameters, all optional. The first indicates the number of rows to move to the array; it defaults to moving all rows. The second is a bookmark indicating the record to start with; the default is to start with the current record. The third is an array of field names or ordinal positions indicating which fields in the recordset should be copied to the array. If you don't specify this parameter, you'll get all the fields.
If you ask for more rows than exist, ADO returns as many as there are. Use the UBound function to find out how many rows were actually returned:
intRows = UBound(varData, 2) + 1
The ", 2" tells UBound to find the number of rows (the second dimension of the array); then you must add 1 to the result because the array is zero-based.
Tip
Be careful when creating your recordset before calling the GetRows method. Because Access will copy all the columns, including memos and long binary fields, you may want to exclude large fields from the recordset before you create the array; they can consume large amounts of memory and can be slow to load. You can also use the Fields parameter to specify exactly which fields to copy to the array.
The code in Listing 6.13 (from basRecordset) fills an array with data and then prints it out, in reverse order.
Listing 6.13
Public Sub TryGetRecords()
' Use an array to process data in a recordset
Dim rst As ADODB.Recordset
Dim varData As Variant
Dim intCount As Integer
Dim intI As Integer
Set rst = New ADODB.Recordset
' Note that we can't move backwards through
' this recordset.
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.Open "tblCustomers"
' Get all the rows, but only the CompanyName
' and ContactName fields.
varData = rst.GetRows( _
Fields:=Array("CompanyName", "ContactName"))
rst.Close
Set rst = Nothing
' How many rows did it actually send back?
intCount = UBound(varData, 2) + 1
' Loop through all the rows, printing out the
' data
For intI = intCount - 1 To 0 Step -1
Debug.Print varData(0, intI), varData(1, intI)
Next intI
End Sub
Tip
Another way to enable movement in reverse, when you have an OLE DB provider that only supplies forward-only recordsets, is to set the CursorLocation property of the recordset to adUseClient. As we discussed earlier in the chapter, this technique is most useful with providers other than Jet.
Creating a Recordset Based on a Command Object
If you need to create a recordset based on any select query (about which you might know nothing at all until your program is running), you must be prepared to supply the recordset with all the parameters the query requires. Without ADO, doing so requires knowing in advance what the parameters are and supplying their values in your code. Using ADO, you can loop through all the parameters of your query and evaluate the necessary parameters. Command objects provide a useful Parameters collection, each element of which represents a single query parameter.
When you open a form based on a query that requires parameters, Access does the work of resolving all the parameter values for you. When you do the same thing in code, however, Access cannot fill in the parameter values for you, even if the parameter values are available to Access. It's up to you to supply those values for the Command object before you attempt to create the recordset.
Tip
Your query won't be able to run at all unless all the necessary parameter values are available. If your query uses form objects as parameters, for example, you need to make sure the appropriate form is open and running, with appropriate values filled in, before you attempt to run a query based on those parameters.
Code like that shown in Listing 6.14 (from basRecordset) will work with any Command object that represents a select query, as long as its parameters pull their values from controls on forms and the necessary forms are open when you run this code.
Listing 6.14
Public Sub DemoParameters()
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim cat As ADOX.Catalog
Dim prc As ADOX.Procedure
' Before running this procedure, open
' frmInfo and enter a value, like "Berlin",
' into the City text box, then tab off of the text box.
' You must move the focus out of the City text box in
' order for this to work.
Set cat = New ADOX.Catalog
' Retrieve the catalog info for the query
cat.ActiveConnection = CurrentProject.Connection
Set prc = cat.Procedures("qryCustCity")
' Get a command object from the Procedure
Set cmd = prc.Command
' Loop through the parameters
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm
' And populate the recordset
Set rst = cmd.Execute
Do Until rst.EOF
Debug.Print rst.Fields(0).Value
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set prc = Nothing
Set cmd = Nothing
Set cat = Nothing
End Sub
The code loops through all the parameters of the object (and there may be none, in which case the loop won't ever execute), pointing a Parameter variable at each of the parameters for the command, one at a time. For each parameter, the code evaluates the Name property using the Eval function and assigns the return value to the Value property of the parameter. This retrieves the value of each parameter without your having to know in advance where the parameter is getting its value.
For example, if your query has a single parameter on the City field:
Forms!frmInfo!CityField
the command contains a single parameter object, for which the Name property is Forms!frmInfo!CityField. Through the use of the Eval function, the code in the example retrieves the value stored in that field and assigns it to the Value property of the specific parameter object. This satisfies the needs of the Command object, and you'll be able to create the recordset you need, based on that Command. The Incremental Search example in Chapter 7 uses this mechanism to allow the underlying code to create a recordset on almost any select query, whether or not it requires parameter values.
WARNING
Note the unexpected manner in which this code retrieves the Command object: it uses ADOX to retrieve the Command property of a Procedure object (that is, a stored query). Normally, you would create a Command object and assign its CommandText property to be the name of a saved query in an Access database. However, this method only works for queries with no parameters. The Jet provider does not properly populate the Parameters collection of a Command object unless you retrieve that Command object via ADOX. Therefore, if you want to satisfy the parameters required by a Command object based on an Access query programmatically, you'll need to use ADOX to retrieve that Command object.
Finding Specific Records
You handle the task of finding specific data in a recordset in different ways, depending on the type of the recordset. Recordsets using the adCmdTableDirect option can use an indexed search to find data, but other recordsets cannot.
Finding Data in a Direct Table Recordset
If you've created a direct table access recordset object, you can use the fast Seek method to locate specific rows. (Attempting to use the Seek method with any other recordset results in a runtime error.) You must take two specific steps to use the Seek method to find data:
- Set the recordset's Index property. This tells Access through which index you'd like it to search. If you want to use the primary key for searching, you must know the name of the primary key. (It's usually PrimaryKey, unless your application has changed it.)
- Use the Seek method to find the value you want, given a seek option and one or more values to search for. The search operator must be one of the intrinsic constants shown in Table 6.10. To indicate to Access what it needs to find, you supply one or more values corresponding to the keys in the index you selected. If you based your index on one field, you need to supply only one value here. If your index includes multiple columns, you must supply all the values in an array, which you can construct with the VBA Array function.
WARNING
Seek only works with recordsets whose CursorLocation property is set to something besides adUseClient. Because adUseServer is the default cursor location, this isn't normally a problem, but be aware that this won't work for client-side cursors.
TABLE 6.10: Seek Options
| SEEK OPTION | MEANING |
|---|---|
| adSeekAfterEq | Seek the key equal to the value supplied, or if there is no such key, the first key after the point where the match would have occurred. |
| adSeekAfter | Seek the first key after the point where a match occurs or would occur. |
| adSeekBeforeEq | Seek the key equal to the value supplied, or if there is no such key, the first key before the point where the match would have occurred. |
| adSeekBefore | Seek the first key before the point where a match occurs or would occur. |
| adSeekFirstEq | Seek the first key equal to the value supplied. |
| adSeekAfterEq | Seek the last key equal to the value supplied. |
If the Seek operation succeeds, the current row will be the row that was found by ADO. If the seek operation fails (that is, ADO didn't locate the row you requested), the recordset's EOF property will be True.
For example, if your database contained an index named PrimaryKey (the default name that Access assigns to primary key indexes), and you wanted to find the first customer with a primary key value greater than or equal to "BBBBB", the following fragment could get you to the correct row:
rst.Index = "PrimaryKey"
rst.Seek "BBBBB", adSeekAfterEq
The values you send to the Seek method must match the data types of the values in the index. In this case, the value is a string. Had it been numeric or a date, you would have needed to use matching data types in the call to the Seek method.
Once you've used the Seek method to find a row, you must, without fail, use the recordset's EOF property to check that you actually found a row. The following code expands on the previous fragment, handling the success or failure of the seek (see SeekExample in basRecordset for the full procedure):
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open "tblCustomers", Options:=adCmdTableDirect
rst.Index = "PrimaryKey"
rst.Seek "BBBBB", adSeekAfterEq
If rst.EOF Then
MsgBox "Unable to find a match!"
Else
MsgBox "The customer name is: " & rst("CompanyName")
End If
Tip
The Seek method always starts at the beginning of the recordset when it searches. Therefore, using Seek inside a loop, searching for subsequent rows that match the criteria, is generally fruitless. Unless you modify the value once you find it so that further searches no longer find a match on that row, your loop will continually find the same row.
Finding Data Using the Find Method
Most recordsets cannot use the Seek method for finding data. Because these recordsets might well be based on ordered subsets of the original data or supplied by providers that don't support indexed recordsets, ADO can't always use an index to speed up the search. Therefore, a search involving a recordset might be a linear search, visiting every row in the recordset until it finds a match. ADO will use an index if it can.
On a bright note, however, ADO provides greater flexibility in general recordset searches than it does when using the Seek method. The flexibility of the Find method allows you to optimize the search so it has to look through the smallest number of rows to find the data it needs. Because you can use Find to continue searching with the record following the previous match, you won't need to start back at the beginning of the recordset to find subsequent matches. In addition, you can use loops to walk your way through the records because you can restart the search without going back to the first row.
WARNING
If you're moving to ADO from DAO, you'll notice the absence of all the various Find methods: FindFirst, FindNext, FindPrevious, and FindLast. These are all rolled up into the single Find method, but replacing all those methods requires careful use of the Find method's parameters. Specifically, the SearchDirection and Start parameters indicate in which direction to search and where to start.
If Find succeeds, the found row will be the current row. If it fails, the recordset's EOF property is true—that is, you're moved to the end of the recordset.
The syntax for the Find method is:
Recordset.Find criteria, SkipRows, SearchDirection, Start
All but the first parameter are optional. The four possible parameters are:
- Criteria is a WHERE clause formatted as though in a SQL expression, without the word WHERE. For example, "OrderId = 5". This can only consist of a single field name, a comparison operator, and a value. Find does not handle complex WHERE clause values. String literals must be surrounded with single quotes, and dates must be surrounded with "#" characters. You can also use the Like operator, with the wildcard character "*".
- SkipRows specifies the offset from the current row where the search should begin. It defaults to starting with the current row.
- SearchDirection can be adSearchForward (the default) or adSearchBackward.
- Start is an optional bookmark where the search should begin. The default is to begin with the current row. (See the section "Using Bookmarks" later in the chapter for more information.)
For example, the following fragment searches for a contact title of "Owner," looking forward from the current row.
rst.Find "ContactTitle = 'Owner'"
Just as with the Find method, you must follow every call to a Find method with a check of the recordset's EOF property (or BOF property, if SearchDirection is adSearchBackward). If that property is True, there is no current row, and the search has failed. Often, when performing some operation that requires looping through all the rows that match some criteria, you can use code like that in Listing 6.15 (from basRecordset).
Listing 6.15
Public Sub FindExample()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
strCriteria = "ContactTitle = 'Owner'"
rst.Open "tblCustomers", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable
With rst
.Find strCriteria
Do While Not .EOF
' There's a matching row,
' do something with it
Debug.Print .Fields("CompanyName")
' Skip the current record
' and continue searching
.Find strCriteria, 1
Loop
End With
rst.Close
Set rst = Nothing
End Sub
Of course, many such loops can be replaced with action queries, which are almost always a better solution to the given programming problem.
WARNING
Find criteria treat Null differently from the way that Access does. Because the ADO Find method does not understand the IsNull() function, the correct way to search for a Null using the Find method is with an expression such as "FieldName = Null" or "FieldName <> Null." This goes against all normal Access handling of Null (where Null doesn't equal anything, not even itself), but it's the only way to make Find work with Null values.
Using Variables in Strings
In building criteria for Find methods and in several other places in VBA (when calling domain functions and when creating SQL strings, for example), you often need to embed variable values into a string. Because ADO (and Jet, for that matter) has no way of finding the value of VBA variables, you need to supply their values before you ask it to do any work for you. This can cause trouble, because Access requires delimiters (quotes for strings, # for dates) around those values, but they aren't part of the variables themselves. This causes many Access developers, experts and neophytes alike, a great deal of anguish.
Numeric values require no delimiters at all, and you can simply represent a string variable using an expression like this:
"[NumericField] = " & intNumber
Date variables need to be delimited with # in an expression. The general solution for the date problem would be
"[DateField] = #" & varDate & "#"
That's not so bad!
The difficulty arises when you attempt to embed a variable containing a string value inside a string. For example, imagine you have a variable named strName that contains the name you'd like to match in your call to the Find method (for the sake of simplicity here, "Smith"). You need to build a string that represents the required WHERE clause:
[LastName] = 'Smith'
As a first attempt, you might try this:
strCriteria = "[LastName] = strName"
When you attempt to run the search, Access complains with a runtime error. The problem is that the expression in strCriteria was this:
[LastName] = strName
Most likely, no one in your table has that particular last name.
As a second attempt, you might try a new approach:
strCriteria = "[LastName] = " & strName
When you attempt to run the search this time, Access again complains with a runtime error. In this case, it was using the value
[LastName] = Smith
which won't work because ADO expects string values to be enclosed in apostrophes.
It should be clear by now that you need to get the apostrophes into that string. ADO provides a solution to this problem.
All the solutions need to arrive at a value for strCriteria that looks like this:
[LastName] = 'Smith'
WARNING
With the old DAO FindFirst method, you had several other alternatives to solving this problem, based on the fact that DAO would accept double-quote characters to delimit a string in a FindFirst criterion. However, ADO requires single quotes in this context.
The solution involves including the apostrophes in the string. You can think of this as a three-step process:
[LastName] = 'Smith'
becomes the string
"[LastName] = 'Smith'"
which can be broken up into several strings
"[LastName] = '" & "Smith" & "'"
which becomes (finally)
"[LastName] = '" & strName & "'"
The main problem with this solution (which many developers use) is that the value stored in strName cannot contain an apostrophe. If it did, you'd end up with an apostrophe embedded within a string that's enclosed in apostrophes. That's not allowed in ADO syntax. Therefore, you can use this method only when strName contains a value that could never contain an apostrophe.
A General Solution for Strings (Well, Almost)
So what can you do when your string contains an apostrophe? As you've just seen, this won't work:
strCriteria = "[CompanyName] = '" & strName & "'"
because the string might contain an apostrophe, too.
It turns out that embedded apostrophes are not really a problem. Because ADO interprets two apostrophes inside a string as a single apostrophe, and VBA accepts this as well, you can just double the delimiter to "escape" it and turn it into a single delimiter within the string. In this case, you can modify strName by doubling any apostrophes in it.
To do this, you'll need a function that accepts a string value and the delimiter character as parameters and returns the string with any occurrences of the delimiter inside it "doubled up." You'll find that function, adhHandleQuotes, in basHandleQuotes in CH06.MDB. It can solve the previous problem:
strCriteria = "[CompanyName] = " & _
adhHandleQuotes(strName, "'")
The adhHandleQuotes function looks for all the delimiter characters inside strName, doubles them, and returns the string, surrounded with the delimiter character. If you're interested in seeing how adhHandleQuotes works, look in basHandleQuotes; the code uses simple string manipulations. If you want to use this technique in your own applications, just import the basHandleQuotes module and call adhHandleQuotes yourself.
Unfortunately, this still isn't the end of the story. Although ADO allows doubling an apostrophe to escape it within a string, it only allows this once per string. So, you can't use the adhHandleQuotes function to manage a search for a company with this name:
StrName = "Ed's And Mary's Cafe'"
There's a solution to this problem, too. Although it's undocumented, you can use # as a character to quote a string that has multiple apostrophes embedded in it. In this case, you can build your criteria string like this:
strCriteria = "[CompanyName] = " & _
"#" & strName & "#"
So, does that solve the general problem? No! Apparently no one at Microsoft considered the problem of strings containing both multiple apostrophes and pound signs. It's unlikely that this will be your search string
StrName = "Joe's #9 Winner's Grill"
but if it is, give up. You can't quote that string with apostrophes, because it has two apostrophes in it. You can't quote it with pound signs because it has a pound sign in it. If you need to be able to handle absolutely any search string, your best bet may be to use the undocumented pound-sign quoting and just hope that you don't get a string containing pound signs to search for.
To test out all these options, try out the FindWithVariables procedure (from basRecordset), shown in Listing 6.16.
Listing 6.16
Public Sub FindWithVariables()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Dim strName As String
Set rst = New ADODB.Recordset
strName = "Alfreds Futterkiste"
' This will give error 3001:
' strCriteria = "[CompanyName] = strName"
' So will this:
' strCriteria = "[CompanyName] = " & strName
' This worked in DAO, but doesn't in ADO, because ADO
' doesn't allow double-quotes for quoting
' strCriteria = "[CompanyName] =""" & strName & """"
' This works -- unless your string contains
' an apostrophe!
strCriteria = "[CompanyName]='" & strName & "'"
rst.Open "tblCustomers", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable
rst.Find strCriteria
If Not rst.EOF Then
Debug.Print rst.Fields("CustomerID")
End If
' Now let's try an apostrophe:
strName = "La corne d'abondance"
' The previous solution gives error 3001 again,
' because of the embedded apostrophe
' strCriteria = "[CompanyName]='" & strName & "'"
' But we can double the embedded apostrophe:
strCriteria = "[CompanyName]=" & _
adhHandleQuotes(strName)
rst.Find strCriteria
If Not rst.EOF Then
Debug.Print rst.Fields("CustomerID")
End If
' What about two apostrophes?
strName = "Joe's and Ed's Grocery"
' This gives error 3001...ADO only understands
' ONE doubled apostrophe
strCriteria = "[CompanyName]=" & adhHandleQuotes(strName)
rst.Find strCriteria
' This is undocumented, but it works:
strCriteria = "[CompanyName]=#" & strName & "#"
rst.Find strCriteria
If Not rst.EOF Then
Debug.Print rst.Fields("CustomerID")
End If
' But what about this one...
strCriteria = "Joe's #9 Winner's Grill"
' You're sunk! There's no way to delimit this variable
' so that ADO can find it. More than one apostrophe, or
' any "#" within the string makes it impossible
' for ADO to find this string.
rst.Close
Set rst = Nothing
End Sub
Using Bookmarks
One of the primary functions needed in any database product is the ability to move quickly to a specified row. ADO provides a number of ways to move about in recordsets, as seen in the section "Moving through a Recordset" earlier in this chapter. In addition to the methods presented there, ADO provides the Bookmark property, which allows you to quickly preserve and restore the current location within a recordset.
What Is a Bookmark?
Every active recordset maintains a single current row. To retrieve a reference to that row, you can store the Bookmark property of the recordset, representing the current row, into a Variant variable. The bookmark itself is a 4-byte–Long integer, the exact value of which is of no particular importance to you. ADO uses the value, but you won't ever look at, or care about, the particular value. You can perform three basic operations with bookmarks:
- Retrieve the value of the bookmark, in order to store it for later retrieval
- Set the value of the bookmark to a previously stored value, effectively setting the current row to be the row where you were when you originally saved the bookmark
- Compare two bookmarks, in order to determine their relative positions within the recordset
You can retrieve and store as many bookmarks for a given recordset as you wish to maintain, but at any given moment, the recordset's Bookmark property always represents the current row. Manipulating bookmarks in ADO is the fastest way to maneuver through rows. For example, if you need to move from the current row and then move back to it, you can use one of two methods:
- Store the primary key value: Move from the row and use the Seek or Find method to move back to the original row, using the saved primary key value to find the row.
- Store the bookmark: Move from the row and then use the bookmark to move back to the original row.
The second method, using the bookmark, is generally much faster than the first. The code to do this might look something like the following example:
Dim varBookmark as Variant
varBookmark = rst.Bookmark
' Move to the first row.
rst.MoveFirst
'
' Now do whatever you moved from the current row to do.
'
' Then move back to the original row.
rst.Bookmark = varBookmark
Note
You might wonder why a bookmark must be stored in a Variant and not a Long integer. A bookmark really is a Long integer, but it's a special kind of Long integer: an unsigned Long integer. That is, the value of a bookmark can vary between 0 and 4294967295. VBA's Long integer values are always signed and can store values between -2,147,483,648 and 2,147,483,647. As you can see, attempting to store a bookmark value in a Long integer type would sometimes fail. Storing the value in a Variant, however, avoids this problem.
Bookmarks and Record Numbers
If you're moving to Access from an Xbase environment, you might be tempted to think of bookmarks as a replacement for record numbers. In reality, that's not the case. Because Access is set-based, row numbers really have no validity here. Access neither stores nor maintains a record number in its data, and you can't count on a bookmark to act as a permanent locator for any given row. Once you close a recordset, the bookmark value is no longer valid. In addition, you cannot use bookmarks as locators across different recordsets, even though the recordsets might be based on the same data and might contain the same rows in the same order. On the other hand, as stated in the preceding section, bookmarks provide an excellent means of moving about in an open recordset.
To Bookmark or Not to Bookmark
Not all ADO recordsets support the Bookmark property. Some data sources make it impossible for ADO to maintain bookmarks, so it is your responsibility as a developer to check whether a recordset supports bookmarks before attempting to use bookmarks with that recordset. To do this, you can use the Supports method, discussed earlier in this chapter.
Also, be aware that there is no valid bookmark when you've positioned the current row to be the "new" row in a recordset. That is, the following code (from basRecordset) will trigger runtime error -2147217887, "Errors occurred":
Public Sub BookmarkError()
Dim rst As ADODB.Recordset
Dim varBookmark As Variant
Set rst = New ADODB.Recordset
rst.Open "tblCustomers", _
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
rst.AddNew
varBookmark = rst.Bookmark
rst.Close
Set rst = Nothing
End Sub
Note
Many ADO error conditions raise the all-purpose "Errors occurred" error. Good luck figuring out what this means. We haven't found any way to determine the actual error when we get this error value.
Comparing Bookmarks
You may occasionally want to know the relative position of one bookmarked record in relation to another bookmarked record. Because bookmarks are, as far as we're concerned, arbitrary values, you cannot reliably use standard comparison operators for this task. Instead, ADO supplies a CompareBookmarks method of a Recordset object. Given two bookmarks stored away for rows in the same recordset, the CompareBookmarks method returns one of the following values:
- adCompareLessThan The first bookmark is before the second.
- adCompareEqual The bookmarks are equal.
- adCompareGreaterThan The first bookmark is after the second.
- adCompareNotEqual The bookmarks are not equal and not ordered.
- adCompareNotComparable The bookmarks cannot be compared.
You might use the CompareBookmarks method like this:
Select Case rst.CompareBookmarks(varBM1, varBM2)
Case adCompareLessThan
' The first row is earlier than the second
Case adCompareEqual
' Both bookmarks point to the same record
Case adCompareGreaterThan
' The second row is earlier than the first
Case adCompareNotEqual
' The records are not the same
Case adCompareNotComparable
' The bookmarks are not from the same recordset
End Select
The Clone Method
Every open recordset supports only a single current row. For bookmarkable recordsets, you can use the Bookmark property to set and retrieve a value corresponding to that current row. If you need to refer to the same recordset in two different ways with two different current rows, you can use the Clone method to create a clone of a recordset. (To retrieve a clone of a form's recordset, use either the form's RecordsetClone or Recordset property instead of the Clone method. See Chapter 8 for more information.) With a clone of the original recordset, you can effectively maintain two separate current rows. This way, you can compare the values in two of the rows in the recordset, for example.
You might be tempted to ask, "Why use the Clone method instead of just creating a new recordset based on the same source?" The answer is clear: creating a recordset clone is faster, in most cases, than creating a new Recordset object. When the source of the data is a query, the difference can be measurable. Rather than reexecuting the entire query to produce the new recordset, the Clone method just points a separate object variable at the original set of rows. This effectively gives you two current rows and two bookmarks, based on the same data. You can also assign the bookmark from one recordset to its clone, because they really are the same recordset.
Be aware of these issues:
- You can only clone a recordset that supports bookmarks.
- A recordset created with the Clone method has its first record as the current record.
- To set a specific row as the current row, use any of the Find or Move methods (Find, MoveFirst, and so on) or set the recordset's Bookmark property with a value retrieved from the original recordset. Remember that bookmark assignments work only when applied to identical recordsets (as are the original and its clone).
- Using the Close method on either the original recordset or its clone doesn't affect the other recordset.
- Changes you make to one clone are immediately reflected in all other clones of the same recordset. However, if you execute the Requery method on a recordset, it will no longer be synchronized with its clones.
The Clone method takes one optional parameter, which indicates the type of locking you desire for the new recordset:
Set rstNew = rstOriginal.Clone(LockType)
The LockType parameter can be one of two intrinsic constants:
- adLockUnspecified, the default, means that the clone inherits the locking behavior of the original recordset.
- adLockReadOnly creates the clone as read-only, regardless of the original recordset's locking.
Sorting Recordsets
When using recordsets as part of your applications, you'll often need to present the rows in a specific order. ADO treats direct table recordsets differently from other recordsets. For all objects, however, remember that if you want a particular sort order, you must specify it yourself.
Sorting Direct Table Recordsets
For table-type recordsets, you can specify the ordering by setting the Index property. (Access does not allow you to set the Index property of any other type of recordset. Attempting to do so will only get you runtime error 3251, "The operation requested by the application is not supported by the provider.") As soon as you set that property, the rows appear in their new ordering.
Listing 6.17 shows a function that lists the fields in the index, in index order, for each index in a specified table. ListIndexFields does its work by looping through the ADOX Table object's collection of indexes. For each index in the collection, it gathers up the index name and the column names and uses them to set the index and print out the value of each field for each row in the recordset. To test ListIndexFields, you might want to create a table with just a few rows and create an index for a few of the columns. Then, in the Immediate window, enter:
? ListIndexFields("YourTableName")
replacing YourTableName with the name of your table. This should show all the indexes in your table, with the first indexed field in indexed order. (Look for ListIndexFields in basRecordset in CH06.MDB.)
Listing 6.17
Public Sub ListIndexFields( _
strTableName As String)
Dim rst As ADODB.Recordset
Dim cat As ADOX.Catalog
Dim idx As ADOX.Index
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strField As String
Set rst = New ADODB.Recordset
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTableName)
rst.Open strTableName, _
CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect
' List values for each index in the collection
For Each idx In tbl.Indexes
' Set the index to use in the recordset
rst.Index = idx.Name
' The index object contains a collection of columns,
' one for each column the index contains
Debug.Print
Debug.Print "Index: " & rst.Index
Debug.Print "============================="
' Move through the whole recordset, in index order,
' printing out the index fields, separated with tabs
rst.MoveFirst
Do While Not rst.EOF
For Each col In idx.Columns
strField = strField & vbTab & rst(col.Name)
Next col
If Len(strField) > 0 Then
strField = Mid$(strField, 2)
End If
Debug.Print strField
strField = ""
rst.MoveNext
Loop
Next idx
rst.Close
Set tbl = Nothing
Set rst = Nothing
Set cat = Nothing
End Sub
Sorting Other Recordsets
Just as with direct table recordsets, unless you specify a sorting order for other recordsets, the rows will show up in an indeterminate order. The natural order for these derived recordsets is a bit more complex because it might depend on more than one table and on the OLE DB provider that's supplying the original data. In any case, if you need a specific ordering, you must set up that ordering yourself.
To create sorted recordsets, you have two choices, described in the next two sections.
Using a SQL ORDER BY Clause
You can create a Recordset object using a SQL statement including an ORDER BY clause. To do so, specify the SQL expression as the row source for the recordset's Open method. For example, the procedure in Listing 6.18 (from basRecordset) creates a recordset based on tblCustomers, including all the columns, sorted by the ContactName column:
Listing 6.18
Public Sub TestOrderBy()
Dim rstSorted As ADODB.Recordset
Set rstSorted = New ADODB.Recordset
rstSorted.Open _ "SELECT * FROM tblCustomers ORDER BY [ContactName];", _ CurrentProject.Connection
Do Until rstSorted.EOF
Debug.Print rstSorted("ContactName")
rstSorted.MoveNext
Loop
rstSorted.Close
Set rstSorted = Nothing
End Sub
Using the Sort Property
You can also set the Sort property of any recordset to change its sort order. The Sort property must be a string in the same style as the ORDER BY clause of a SQL expression (that is, a comma-separated list of field names, optionally with ASC or DESC to indicate ascending or descending sorts). You must specify the column on which to sort and, optionally, the ordering. The following fragments show how to set the Sort property:
rst.Sort = "LastName" ' Defaults to ascending
rst.Sort = "LastName ASC" ' Ascending sort
rst.Sort = "LastName DESC" ' Descending sort
' Sort on two fields, LastName in ascending order, and
' FirstName in descending order.
rst.Sort = "LastName ASC, FirstName DESC"
Here are some items to remember when using the Sort property:
- The new sort order takes effect immediately when you change the property.
- Because the Jet provider doesn't support the necessary OLE DB interfaces for sorting, you must set the CursorLocation property of the recordset to adUseClient in order to use the Sort property. This isn't necessarily true for other providers, but it does limit the usefulness of this property for Jet recordsets.
- It might be faster to open a new recordset based on a SQL expression than to use the Sort property.
The procedure in Listing 6.19, from basRecordset, opens a recordset and sorts it in two different ways, printing out the primary key value from the first row each time.
Listing 6.19
Public Sub TestSort()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
' MUST use client-side cursors for sorting, for Jet.
' SQL Server supports the Sort method on server-side
' cursors, as well.
rst.CursorLocation = adUseClient
rst.Open "tblCustomers", _
CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic
' You'll see different CustomerID values,
' because the sort order will have changed.
rst.Sort = "CompanyName"
Debug.Print rst("CustomerID")
rst.Sort = "City"
Debug.Print rst("CustomerID")
rst.Close
Set rst = Nothing
End Sub
Filtering Recordsets
Just as with sorting a recordset, you have two choices if you want to create a filtered subset of rows. These choices are outlined in the next two sections. You'll need to decide which method to use based on the circumstances of your application.
Using a SQL WHERE Clause
You can create a recordset by using a SQL statement including a WHERE clause. To do so, specify the SQL expression as the row source for the recordset's Open method. For example, this fragment:
rstFiltered.Open _
"SELECT * FROM tblCustomers WHERE [ZipCode] = '90210'"), _
CurrentProject.Connection
creates a recordset based on all the columns in tblCustomers, including only the rows where the ZipCode field is "90210".
Using the Filter Property
You can also set the Filter property of any recordset to change the set of rows it contains. The Filter property must be a string in the same style as the WHERE clause of a SQL expression. The filtering takes effect immediately. For example, you generally use the Filter property like this:
' rst is an existing recordset.
rst.Filter = "[Age] > 35"
' Now rst contains all the rows from rst that
' have an [Age] field greater than 35.
Here are some items to remember when using the Filter property:
- The new filtering takes effect immediately.
- It might be faster to open a new recordset based on a SQL expression than to use the Filter property.
- All the issues described in the section "Using Variables with Strings" apply here, because you're likely to want to build your filter string programmatically from variables.
- The new filtering will never retrieve additional rows from the original source tables. It will filter only rows that are already in the recordset.
In addition to a WHERE clause, you can use two other types of information for a filter:
- You can supply an array of bookmarks that are valid in the current recordset to filter the recordset to show only those records.
- You can supply one of the intrinsic constants listed in Table 6.11.
For example, to remove any existing filter from a recordset you can use this code fragment:
rst.Filter = adFilterNone
TABLE 6.11: Filter Constants
| CONSTANT | MEANING |
|---|---|
| adFilterNone | Remove any existing filter, showing all records in the original recordset. |
| adFilterPendingRecords | Filter the recordset to contain only records that have been changed but not yet saved. |
| adFilterAffectedRecords | Filter the recordset to contain only records that were affected by the last Delete, Resync, UpdateBatch, or CancelBatch method. |
| adFilterFetchedRecords | Filter the recordset to only show the records most recently fetched to the local cache. |
| adFilterConflictingRecords | Filter the recordset to contain only the records that failed to commit during a batch update operation. |
Refreshing Recordsets
Most ADO recordsets you create will only contain your own changes to the data in the recordset. But because Access is a multiuser database and other users can add, edit, and delete records, your copy of a recordset can get increasingly out of synch with what's actually stored in the database. ADO provides two methods to allow you to update your recordset to match what's actually in the database: Requery and Resync.
The Requery method is roughly the equivalent of calling the recordset's Close method and then calling its Open method again with the original arguments. This method causes the underlying OLE DB provider to carry out the steps that it originally used to retrieve the data all over again. Note that if you need to change any of the properties that govern the behavior of the recordset (CursorLocation, CursorType, or LockType), you can't use the Requery method. Instead, you'll need to explicitly close the recordset, change the properties, and then call the Open method to repopulate the recordset.
The Resync method resynchronizes the records in your recordset with the underlying database. Thus, calling the Resync method on a recordset won't show you records added by other users. It will, however, pick up any changes other users have made and make any deleted records invalid in your recordset. Because it doesn't have to execute the original query all over again, Resync is faster than Requery.
WARNING
Not all recordsets support the Resync method. You may want to call the Supports method (see the section "The Supports Method" earlier in this chapter for more information) using the adResync constant to verify that this method will work before using it.
Editing Data in a Recordset Object
Of course, almost any database application needs to be able to add, update, and delete data. ADO provides methods for accomplishing each of these tasks. The next few sections discuss the various data-manipulation methods that ADO supports.
When Is a Recordset Modifiable?
When you open a recordset, it may only be possible to retrieve the data for viewing. If so, your attempts to modify the data will result in a runtime trappable error. You can edit direct table recordsets, unless someone else has placed a lock on that table (opened it exclusively or created a recordset based on it with an option that precludes others from changing its data). You can edit other recordsets unless locks have been placed by other users. In addition, join rules may prevent editing of certain fields. Of course, read-only recordsets are never modifiable.
Changing Data in a Recordset
To programmatically change the data in any recordset (assuming the recordset is updatable), take the following steps:
- Move to the desired row.
- Make changes.
- Optionally, use the Update method to save the edits.
Calling the Update method of the recordset is generally not necessary, but we highly recommend that you don't skip this step. If you modify a row and then leave that row, ADO automatically updates the row for you. If you modify a row and then attempt to close the recordset without explicitly updating the row's data, however, ADO will trigger a runtime error. Therefore, we recommend that you always call the Update method. This not only makes your code more explicit (that is, it's obvious from reading the code what it's doing), but it means you needn't worry about whether the row got committed or not, before you attempt to close the recordset.
If ADO automatically commits a row when you leave that row, how can you leave a row and discard the changes? To do that, call the recordset's CancelUpdate method. This method tosses out any changes you've made to the current row.
The code in Listing 6.20 (from basRecordset) finds the first row in a recordset in which the ContactTitle field contains "Owner" and changes it to "Manager".
Listing 6.20
Public Sub ModifyRow()
' Demonstrate how you modify data.
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Source = "tblCustomers"
rst.Open Options:=adCmdTable
With rst
.Find "[ContactTitle] = 'Owner'"
If .EOF Then
MsgBox "No Match was Found!"
Else
.Fields("ContactTitle") = "Manager" .Update
End If
End With
rst.Close
Set rst = Nothing
End Sub
WARNING
If you're a DAO programmer, note the serious differences between DAO and ADO on the issue of updating data. First of all, DAO requires you to call the Edit method of the recordset before you start making changes. ADO doesn't require this and doesn't even provide an Edit method. In DAO, if you leave a row without calling the Update method of the recordset, your changes are automatically discarded. In ADO, if you leave a row without calling the Update method, your changes are automatically saved (unless you call the CancelUpdate method to cancel your updates). These two changes will challenge every Access developer moving code from DAO to ADO. Be wary of these differences!
Batch Updates
If you use the client-side cursor library with a keyset or static cursor, you can also take advantage of ADO's ability to perform batch updates. That is, you can edit multiple records in a database and then send all of the updates to the underlying OLE DB provider to be stored in a single operation. The code in Listing 6.21 (from BulkUpdate in basRecordset) finds all records where the contact title is "Owner," changes the title to "Partner," and then sends all of the changes back to the database in a single operation.
Listing 6.21
Public Sub BulkUpdate()
' Demonstrate the BatchUpdate method.
' Note that this requires both a client-side
' cursor, and a LockType property of
' adLockBatchOptimistic.
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
strCriteria = "[ContactTitle] = 'Owner'"
With rst
Set .ActiveConnection = CurrentProject.Connection
.Source = "tblCustomers"
.CursorLocation = adUseClient .LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
.Open
.Find strCriteria
Do While Not .EOF
' There's a matching row,
' so change the title
.Fields("ContactTitle") = "Partner"
' Skip past the current record
' and continue searching
.Find strCriteria, 1
Loop
' Commit all the changes
.UpdateBatch
End With
rst.Close
Set rst = Nothing
End Sub
If any of your changes can't be saved (for example, because another user has deleted the record), a runtime error occurs. In this case, you can use the Filter property with the adFilterAffectedRecords constant to filter the recordset down to only those records that had problems.
Adding New Rows to a Recordset
To programmatically add new rows to a recordset (assuming neither updatability nor security keeps you from doing so), follow these steps:
- Use the AddNew method to add a new row. All fields will be set to their default values.
- Fill in fields as needed.
- Optionally (but suggested), use the Update method to save the new row. If you don't call the Update method but you leave the row, ADO will save it automatically. If you attempt to close the recordset with an update pending but haven't explicitly saved the row, you'll get a runtime error.
Note
The new record becomes the current row as soon as you call the Update method.
The following example adds a new row to the recordset and fills in a few of the fields. Once it's done, it makes the new row the current row:
With rst
.AddNew
.Fields("LastName") = "Smith"
.Fields("FirstName") = "Tommy"
.Update
End With
Tip
We like to indent the lines of code between the calls to the AddNew method and the Update method. This makes it clear, when reading code, that you're working with a pending update. This isn't required, but it's a nice touch.
Deleting Data from a Recordset
To delete a row from a recordset, follow these steps:
- Move to the desired row.
- Use the Delete method to delete it.
Tip
You don't need to use the Update methods when deleting a row. Once you delete it, it's gone—unless, of course, you wrapped the entire thing in a transaction. In that case, you can roll back the transaction to retrieve the deleted row. (See Chapter 2 in Access 2000 Developer's Handbook, Volume II for more information on using transactions.)
Tip
After you delete a record, it's still the current record. The previous row is still the previous row, and the next row is still the next row. Use MoveNext to move to the next row, if that's where you'd like to be. If you attempt to do anything with the row you've just deleted, you'll receive a runtime error.
The code in Listing 6.22 deletes all the rows from a table, although it is not necessarily the best way to solve the problem. In reality, you'd use a Delete query to do the work. To try this function out, check in basRecordset in CH06.MDB.
Listing 6.22
Public Sub ZapTable(strTable As String)
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open strTable, CurrentProject.Connection, _
adOpenStatic, adLockOptimistic
With rst
If .RecordCount > 0 Then
.MoveFirst
Do
.Delete
' Without this MoveNext, ADO would
' continually try to delete the
' same row, the first one.
.MoveNext
Loop Until .EOF
End If
End With
rst.Close
Set rst = Nothing
End Sub
Persisting Recordsets
In previous data access libraries, recordsets have been completely ephemeral. They existed only when opened in memory, and, if you shut down your application, they were automatically destroyed. ADO adds the ability to persist a recordset to a file on disk. In fact, you can persist a recordset, later reopen it, edit it, reconnect it to the original data source, and save changes.
To persist a recordset to disk for later use, you call its Save method:
rst.Save Filename, Format
The Filename parameter is the full path and filename to the file that you wish to use to hold the contents of this recordset. The Format parameter can be one of two intrinsic constants:
- adPersistADTG (default) saves the recordset in the Microsoft proprietary Advanced Data Tablegram format.
- adPersistXML saves the recordset as XML. If you save the recordset in XML format, you can easily use the saved XML file as a data source for another application or control that understands XML (an emerging Internet standard for transferring data).
ADTG files are smaller than XML files, so unless you need the ability to distribute data in XML format, stick to ADTG.
The code in Listing 6.23, from basPersist in CH06.MDB, opens the customers table and then saves the resulting recordset to a disk file in the same folder as the Access database itself.
Listing 6.23
Public Sub SaveRecordset()
Dim rst As ADODB.Recordset
Dim strFile As String
Set rst = New ADODB.Recordset
' Open the recordset from the database
rst.Open "tblCustomers", CurrentProject.Connection, _
adOpenStatic, adLockOptimistic
' Construct a file name to use
strFile = CurrentProject.Path & "\Customers.adtg"
' Destroy any existing file
On Error Resume Next
Kill strFile
Err.Clear
' Now save the recordset to disk
rst.Save strFile, adPersistADTG
' Close the recordset in memory
rst.Close
End Sub
Tip
Calling the Save method will fail if the specified file already exists. The example procedure deletes the file before it attempts to write out a new one. You may want to do this in your own code, as well.
If you apply a filter to a recordset and then save it, only the filtered rows are saved. If you save a recordset and continue to work with it, updating records, your changes are written to the disk file whenever you call the Update method until you call the recordset's Close method. When the Save method is invoked, the current record is reset to the first record in the recordset.
Although the Jet OLE DB provider supplies all of the functionality necessary to save recordsets, this is not true of all providers. If you're using data from another source and want to be sure you can save a recordset, set the CursorLocation property to adUseClient to create a client-side cursor.
To retrieve a saved recordset, you use the Open method of the recordset object. As the Source parameter, you supply the name of the disk file that contains the previously saved recordset. You should not specify a Connection when you reopen the recordset. The procedure in Listing 6.24, from basPersist, demonstrates how you can open a persisted recordset.
Note
Although we've managed to save and open persisted XML recordsets, we haven't managed to update them. Perhaps this limitation will be corrected by the time you read this, but as we were writing, we were unable to successfully update recordsets saved in XML format.
Listing 6.24
Public Sub RetrieveRecordset()
Dim rst As ADODB.Recordset
Dim strFile As String
Set rst = New ADODB.Recordset
' Construct a file name to use
strFile = CurrentProject.Path & "\Customers.adtg"
' Make sure the file exists
If Len(Dir(strFile)) > 0 Then
' Open the recordset from the file
rst.Open strFile, , adOpenStatic, adLockOptimistic
' Show that we've got data
Debug.Print rst.Fields("ContactTitle")
' Reconnect the recordset to the database
rst.ActiveConnection = CurrentProject.Connection
' Make a change and save it
rst.Fields("ContactTitle") = "Sales Rep"
rst.Update
End If
rst.Close
Set rst = Nothing
End Sub
As you can see in the previous code, to reconnect a recordset to a database, you set the recordset's ActiveConnection property to a valid Connection property for the database. Once you've done this, you can update the recordset just like any other recordset.
WARNING
Cursor options are not persisted as part of a saved recordset. Be sure to specify the cursor type and locking type when you reopen a saved recordset. Otherwise, you'll get a default forward-only, read-only recordset.
Tip
You can change the ActiveConnection property for any recordset, not just for one retrieved from a disk file. If you open a recordset from a connection to a database named Nwind.MDB, change the connection to use a database named NwindBAK.MDB, and then make changes, your changes will be saved to NwindBAK.MDB.
Using Recordsets in Place of Arrays
For a long time, Access developers have had to choose whether to use recordsets or arrays for temporary data storage. Arrays could be easily created and destroyed in memory, whereas recordsets required tables to store their data. Using recordsets, on the other hand, meant you could use familiar operations such as Find and Sort to manipulate the temporary data. You could also refer to values using their field names in a recordset.
With ADO, you no longer have to make the tradeoff. ADO allows you to create recordsets in memory, not connected to any table or query, and add data to them. You'll find an example in the RecordsetArray function in basArray. This function starts by declaring a recordset variable, but instead of opening the recordset, it appends two fields to the Fields collection:
Dim rst As ADODB.Recordset
Dim intI As Integer
' Instantiate the recordset.
Set rst = New ADODB.Recordset
' Append two fields
rst.Fields.Append "ColorID", adSmallInt
rst.Fields.Append "ColorName", adVarChar, 10
When you append a field to a new recordset, ADO creates the recordset object in memory and leaves it up to you to specify the structure of the recordset. The Append method for the Fields collection of a recordset works just like the Append method that we covered earlier in the chapter in the section "Creating a New Table" for the Fields collection of a table. In this example, we've created one integer field and one text field and assigned names to both of them.
After you're done appending fields, you can open the recordset and work with it just like any other recordset. You don't need to supply any arguments to the Open method, but you do need to call it. For example, here's the remaining code from the RecordsetArray function. As you can see, there's no difference in manipulating this created recordset compared to any other recordset.
' Put some data in the recordset
rst.Open
rst.AddNew _
Array("ColorID", "ColorName"), _
Array(1, "Red")
rst.AddNew _
Array("ColorID", "ColorName"), _
Array(2, "Orange")
rst.AddNew _
Array("ColorID", "ColorName"), _
Array(3, "Yellow")
rst.AddNew _
Array("ColorID", "ColorName"), _
Array(4, "Green")
rst.AddNew _
Array("ColorID", "ColorName"), _
Array(5, "Blue")
rst.AddNew _
Array("ColorID", "ColorName"), _
Array(6, "Indigo")
rst.AddNew _
Array("ColorID", "ColorName"), _
Array(7, "Violet")
' Save the data. This isn't required,
' because ADO will do it for you.
rst.UpdateBatch
' Dump the recordset to the Immediate Window
rst.MoveFirst
Do Until rst.EOF
Debug.Print rst("ColorID"), rst("ColorName")
rst.MoveNext
Loop
Tip
This example also demonstrates a shortcut for the AddNew method. You can pass the method two arrays, one containing field names and the other containing the data for those fields, to both create the new record and populate it within a single operation. Sometimes, you may find this shortcut method simpler than calling the AddNew method and setting field values individually. You can use the same shortcut with the Update method, if you're changing values in a recordset.
Using Command Objects for Bulk Operations
You've already seen that you can use a Command object as the source for a recordset. You can also use a Command object to execute bulk SQL operations. These operations (the equivalent of Update, Append, and Delete queries in the Access user interface) use the SQL UPDATE, INSERT INTO, or DELETE commands to quickly modify sets of records.
To execute bulk SQL operations, you use the Execute method of the Command object, as in the code from basCommand, in Listing 6.25:
Listing 6.25
Public Sub ExecuteCommand()
Dim cmd As ADODB.Command
Dim lngAffected As Integer
Set cmd = New ADODB.Command
' Set the connection to use for this command
cmd.ActiveConnection = CurrentProject.Connection
' Set the properties of the command
cmd.CommandType = adCmdText
cmd.CommandText = _
"UPDATE tblOrders " & _
"SET ShipCountry = 'United States' " & _
"WHERE ShipCountry = 'USA'"
' And execute it
cmd.Execute lngAffected
Debug.Print lngAffected & " Records modified"
Set cmd = Nothing
End Sub
Tip
Note the first parameter passed to the Execute method: this parameter gets filled in by ADO, so that you can tell how many rows were affected by the Command. Pass a Long integer in this parameter and, after the call to the Execute method, you can use this value to determine what happened while the Command was executing.
You can also specify the adExecuteNoRecords argument, to indicate to ADO that the command does not return any data. You can execute bulk update SQL perfectly well without supplying this argument; however, supplying it does speed up the execution of the command. If the command requires parameters, you can supply them as the second argument to the Execute method. You can also use the same technique to execute a saved action query in your database, as shown in the procedure from basCommand in Listing 6.26.
Listing 6.26
Public Sub ExecuteCommand2()
Dim cmd As ADODB.Command
Dim lngAffected As Long
Set cmd = New ADODB.Command
' Set the connection to use for this command
cmd.ActiveConnection = CurrentProject.Connection
' Set the properties of the command
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qupdCountry"
' And execute it
cmd.Execute lngAffected, , adExecuteNoRecords
Debug.Print lngAffected & " Records modified"
Set cmd = Nothing
End Sub
Note
Note that even though Jet doesn't support stored procedures, you set the CommandType property of the Command object to adCmdStoredProc when you want to run a query that doesn't return any rows.
Finally, if your bulk SQL command does not require any parameters, you can execute it without explicitly creating a Command object. You do this by using the Execute method of the Connection object, which implicitly creates and then executes a Command object. This technique allows you to simplify the code greatly, in this case, from basCommand:
Public Sub ExecuteDirect()
Dim lngAffected As Long
CurrentProject.Connection.Execute "qupdCountry", _
lngAffected, adExecuteNoRecords
Debug.Print lngAffected & " Records modified"
End Sub
Schema Recordsets
In addition to recordsets containing data from the tables in your database, ADO allows you to open recordsets containing information about the database itself, called schema recordsets. For example, you can open a recordset that contains a list of all of the tables in your database, with their descriptions, creation date, and other information. While much of this information is available through ADOX, there are some pieces of information that are most readily retrieved from a schema recordset.
To open a schema recordset, you use the OpenSchema method of the Connection object:
Connection.OpenSchema QueryType, Criteria, SchemaID
- QueryType is one of the intrinsic constants listed in Table 6.12. This tells the method what type of information you'd like.
- Criteria is an optional parameter that lets you filter the resulting recordset. For example, you could filter a recordset of tables to retrieve information on only a particular table.
- SchemaID is a required parameter only when the QueryType is adSchemaProviderSpecific, in which case it is a GUID (a Globally Unique Identifier: a 16-byte value, as is used by Windows to manage installed applications in the Registry) that identifies a schema supported only by one provider.
Table 6.12 shows the available QueryType constants you can use with the OpenSchema method.
TABLE 6.12: Types of Schema Recordsets
| QUERYTYPE | RETURNS |
|---|---|
| adSchemaAsserts | Recordset of constraints in the database. Not supported by the Jet Provider. |
| adSchemaCatalogs | Recordset of all available databases. Not supported by the Jet Provider. |
| adSchemaCharacterSets | Recordset of all character sets supported by the database engine. Not supported by the Jet Provider. |
| adSchemaCheckConstraints | Recordset of validation rules. |
| adSchemaCollations | Recordset of sort orders supported by the database engine. Not supported by the Jet Provider. |
| adSchemaColumnPrivileges | Recordset of column-level security information. Not supported by the Jet Provider. |
| adSchemaColumns | Recordset of information on all fields in all tables. |
| adSchemaColumnsDomainUsage | Recordset of columns dependent on a particular domain. Not supported by the Jet Provider. |
| adSchemaConstraintColumnUsage | Recordset identifying fields used by validation rules and keys. |
| adSchemaConstraintTableUsage | Recordset of information about table-level constraints. Not supported by the Jet Provider. |
| adSchemaCubes | Recordset of information about multidimensional data available from the database. Not supported by the Jet Provider. |
| adSchemaDBInfoKeywords | Recordset of reserved words. |
| adSchemaDBInfoLiterals | Recordset of quoting and escape characters. |
| adSchemaDimensions | Recordset of cube dimensions. Not supported by the Jet Provider. |
| adSchemaForeignKeys | Recordset of foreign key information. |
| adSchemaHierarchies | Recordset of cube hierarchies. Not supported by the Jet Provider. |
| adSchemaIndexes | Recordset of all indexes in the database. |
| adSchemaKeyColumnUsage | Recordset of fields that are contained in any primary or foreign key. |
| adSchemaLevels | Recordset of levels within a cube hierarchy. Not supported by the Jet Provider. |
| adSchemaMeasures | Recordset of cube measures. Not supported by the Jet Provider. |
| adSchemaMembers | Recordset of cube members. Not supported by the Jet Provider. |
| adSchemaPrimaryKeys | Recordset of all primary keys in the database. |
| adSchemaProcedureColumns | Recordset of columns in all stored procedures in the database. Not supported by the Jet Provider. |
| adSchemaProcedureParameters | Recordset of parameters to all stored procedures in the database. Not supported by the Jet Provider. |
| adSchemaProcedures | Recordset of stored procedures. Not supported by the Jet Provider. |
| adSchemaProperties | Recordset of database properties. Not supported by the Jet Provider. |
| adSchemaProviderSpecific | Schema information from a particular OLE DB Provider. You must supply the QueryID value for a particular schema to use this constant. |
| adSchemaProviderTypes | Recordset of data types supported by the provider. |
| adSchemaReferentialConstraints | Recordset of relationships. |
| adSchemaSchemata | Recordset of schemas. Not supported by the Jet Provider. |
| adSchemaSQLLanguages | Recordset of information on ANSI SQL support. Not supported by the Jet Provider. |
| adSchemaStatistics | Recordset of the cardinality (number of rows) in every table in the database. |
| adSchemaTableConstraints | Recordset of table-level validation rules. |
| adSchemaTablePrivileges | Recordset of table-level security information. Not supported by the Jet Provider. |
| adSchemaTables | Recordset of tables. |
| adSchemaTranslations | Recordset of defined character translations. Not supported by the Jet Provider. |
| adSchemaTrustees | Recordset of users. |
| adSchemaUsagePrivileges | Recordset of security information. Not supported by the Jet Provider. |
| adSchemaViewColumnUsage | Recordset of fields in views. Not supported by the Jet Provider. |
| adSchemaViews | Recordset of queries. |
| adSchemaViewTableUsage | Recordset of tables in views. Not supported by the Jet Provider. |
For example, you can use the code in Listing 6.27 (from basSchema) to list all of the tables in your database to the Immediate Window.
Listing 6.27
Public Sub ShowTableSchema()
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set rst = CurrentProject.Connection. _ OpenSchema(adSchemaTables)
For Each fld In rst.Fields
Debug.Print fld.Name & vbTab;
Next fld
Debug.Print
Debug.Print String(130, "-")
Do Until rst.EOF
For Each fld In rst.Fields
If Not IsNull(fld.Value) Then
Debug.Print fld.Value & vbTab;
Else
Debug.Print "(null)" & vbTab;
End If
Next fld
Debug.Print
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set fld = Nothing
End Sub
The Jet Provider also defines four provider-specific schema recordsets:
- A recordset of current users of the database
- A recordset of query performance information
- A recordset of partial replica filters
- A recordset of replica conflict tables
We've included the GUIDs for these recordsets in basSchema so that you can use them in your own code. As an example, the code in Listing 6.28 retrieves the current list of users of the current database. For each user, it shows the user name, the name of the computer at which they're logged in, and whether they've left the database in a suspect (needing repair) state. This sort of information can be very useful if you're trying to determine who's leaving a database open in a multiuser situation.
Listing 6.28
Const JET_SCHEMA_USERROSTER = _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Public Sub ShowUserRoster()
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set rst = CurrentProject.Connection.OpenSchema( _
adSchemaProviderSpecific, , JET_SCHEMA_USERROSTER)
For Each fld In rst.Fields
Debug.Print fld.Name & vbTab;
Next fld
Debug.Print
Debug.Print String(80, "-")
Do Until rst.EOF
For Each fld In rst.Fields
If Not IsNull(fld.Value) Then
Debug.Print fld.Value & vbTab;
Else
Debug.Print "(null)" & vbTab;
End If
Next fld
Debug.Print
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set fld = Nothing
End Sub
Note
You'll see other examples of using Schema recordsets in Chapter 17 of this volume as well as in Access 2000 Developer's Handbook, Volume II.
Using the CurrentProject and CurrentData Collections
When you're developing applications, you'll often need to present users with a list of database objects. You might, for example, want to enumerate all of the reports in your database in a list box, so that the user can select a report to execute. Although this information is all available in the Database window, allowing users unfettered access to this window is dangerous because they can then open and alter objects without constraint. In professional applications, you'll normally want to hide the Database window and use your own interface to let users manipulate only the objects that you want them to use.
Because ADO is a general-purpose interface, it doesn't know about Access objects such as Forms, Reports, Macros, Modules, and Data Access Pages. In order to get around this limitation, the Access designers supplied a number of useful collections as properties of the CurrentProject and CurrentData objects. The CurrentProject object represents all of the user interface (non-data-bearing) objects in your Access database. The CurrentData object represents all of the data-bearing objects in your Access database. Table 6.13 lists these collections.
TABLE 6.13: Database Object Collections
| COLLECTION | PARENT | USEFUL IN MDB? |
|---|---|---|
| AllDataAccessPages | CurrentProject | Yes |
| AllDatabaseDiagrams | CurrentData | No |
| AllForms | CurrentProject | Yes |
| AllMacros | CurrentProject | Yes |
| AllModules | CurrentProject | Yes |
| AllQueries | CurrentData | Yes |
| AllReports | CurrentProject | Yes |
| AllStoredProcedures | CurrentData | No |
| AllTables | CurrentData | Yes |
| AllViews | CurrentData | No |
You'll see that some of these collections are not useful in an Access database. They're intended for use in Access projects using SQL Server or the MSDE engine, which expose some different objects. You'll learn more about these collections in Access 2000 Developer's Handbook, Volume II.
WARNING
Don't confuse the AllForms and AllReports collections with the Forms and Reports collections supplied by Access. The former contain all of the forms and reports in your database, while the latter contain only the forms and reports currently open in the user interface.
Each of these collections is a collection of AccessObject objects. An AccessObject has the following properties:
- Name The name of the object as it appears in the Database window.
- FullName The filename, including path, of a Data Access Page. This property is an empty string for other types of object.
- IsLoaded True if the object is currently open in the user interface, and False otherwise.
- Type An intrinsic constant indicating the type of the AccessObject. It will contain one of acDataAccessPage, acDiagram, acForm, acMacro, acModule, acQuery, acReport, acServerView, acStoredProcedure, or acTable.
- Parent A reference to the containing collection.
In addition, an AccessObject has a Properties collection containing user-defined properties, if any have been added to the object.
Listing 6.29 (from basListObjects) shows how you can use these collections to list all of the objects in an Access database to the Immediate window.
Listing 6.29
Public Sub ListObjects()
Dim aob As AccessObject
With CurrentData
Debug.Print "Tables"
For Each aob In .AllTables
Debug.Print " " & aob.Name
Next aob
Debug.Print "Queries"
For Each aob In .AllQueries
Debug.Print " " & aob.Name
Next aob
End With
With CurrentProject
Debug.Print "Forms"
For Each aob In .AllForms
Debug.Print " " & aob.Name
Next aob
Debug.Print "Reports"
For Each aob In .AllReports
Debug.Print " " & aob.Name
Next aob
Debug.Print "Pages"
For Each aob In .AllDataAccessPages
Debug.Print " " & aob.Name
Next aob
Debug.Print "Macros"
For Each aob In .AllMacros
Debug.Print " " & aob.Name
Next aob
Debug.Print "Modules"
For Each aob In .AllModules
Debug.Print " " & aob.Name
Next aob
End With
End Sub
A Case Study: Using the Collections
As an example of using the CurrentProject and CurrentData collections as part of an application, we've provided a simple replacement for the database window (see Figure 6.7) that you can import into any application. You can use it directly as is, or you can modify it to add new functionality. You might want to remove some of the objects for your own applications. For example, you might like to provide users with a list of only certain tables and queries. Perhaps you don't want to show your users a list of macros or modules. Given frmDBC as a starting place, you can make as many changes as you wish to fit your own needs. The point of the sample is to demonstrate the use of the objects we've discussed in this chapter to handle the objects in a database. In the interest of simplicity, we've modeled this form on the Access 97 database container. If your users are familiar with that interface, they'll be happy with this look. If not, you can certainly use one of the many custom controls available and have Access display this information in a different format. (For a different approach to this problem, see Chapter 17.)
Figure 6.7: The sample database container is simpler than the one you'll find in Access, but it provides many of the same features.
Designing frmDBC
The design considerations for frmDBC were to
- Provide a list of all the tables, queries, forms, reports, pages, macros, and modules in the current database
- Keep the interface as simple as possible
- Allow for customization
The following sections discuss the form itself and how it does its work.
Choosing an Object Type and an Action
When you click one of the tabs in the Tab control (tabObjects), the code attached to the control's Change event refills the list box (lstObjects) that displays the list of objects. The particular list that lstObjects displays depends, of course, on the selected tab.
Tip
To make the code as simple as possible, we've used the intrinsic constants acTable, acQuery, acForm, acReport, acDataAccessPage, acMacro, and acModule wherever possible. Whenever possible, use the Access-defined constants in your code.
Once you've chosen an object type (and forced the list box to refill itself) and selected an object from the list, you can select one of the action buttons at the top of the form (New, Open, or Design). Depending on the circumstances, one or more of those buttons might have a different caption and might be disabled.
Displaying the Object List
To make the form work as simply as possible, frmDBC fills the list box (lstObjects) by providing a semicolon-delimited list of names as the RowSource property for the list box. This requires that the list box's RowSourceType property be set to Value List, and the form's Open event procedure handles that chore. We could have used a list-filling callback function or an ActiveX control to fill the list box, but we decided on this simpler method instead. (See Chapter 7 for more information on list-filling callback functions or using an ActiveX control in place of Access' native list box control.) Because the RowSource property is limited to 2048 characters, the number of objects you can display in this sample form will be artificially constrained. The longer your object names, the fewer you'll be able to display in the list box. (Remember that every entry in the list will have an extra character, its trailing semicolon.) If you can accept that limitation, you may find this example a good starting place.
Filling the Object List
The GetObjectList function is the heart of this entire form. Given the object type to enumerate, it creates a semicolon-delimited list of object names and returns that string. Listing 6.30 shows the entire function, and the following sections go through the code, one bit at time.
Listing 6.30
Private Function GetObjectList( _
ByVal lngType as AcObjecType) As String
Dim intI As Integer
Dim fSystemObj As Boolean
Dim strName As String
Dim fShowHidden As Boolean
Dim fIsHidden As Boolean
Dim strOutput As String
Dim fShowSystem As Boolean
Dim objCollection As Object
Dim aob As AccessObject
On Error GoTo HandleErrors
DoCmd.Hourglass True
' Are you supposed to show hidden/system objects?
fShowHidden = _
Application.GetOption("Show Hidden Objects")
fShowSystem = _
Application.GetOption("Show System Objects")
Select Case lngType
Case acTable
Set objCollection = CurrentData.AllTables
Case acQuery
Set objCollection = CurrentData.AllQueries
Case acForm
Set objCollection = CurrentProject.AllForms
Case acReport
Set objCollection = CurrentProject.AllReports
Case acDataAccessPage
Set objCollection = _
CurrentProject.AllDataAccessPages
Case acMacro
Set objCollection = CurrentProject.AllMacros
Case acModule
Set objCollection = CurrentProject.AllModules
End Select
For Each aob In objCollection
fIsHidden = IsHidden(aob)
strName = aob.Name
fSystemObj = IsSystemObject(aob)
' Unless this is a system object and
' you're not showing system objects...
If (fSystemObj Imp fShowSystem) Then
' If the object isn't deleted and its hidden
' characteristics match those you're
' looking for...
If Not isDeleted(strName) And _
(fIsHidden Imp fShowHidden) Then
' If this isn't a form, just add it to
' the list. If it is, one more check:
' is this the CURRENT form? If so, and if
' the flag isn't set to include the current
' form, then skip it.
Select Case intType
Case acForm
If Not (adhcSkipThisForm And _
(strName = Me.Name)) Then
strOutput = _
strOutput & ";" & strName
End If
Case Else
strOutput = _
strOutput & ";" & strName
End Select
End If
End If
Next aob
strOutput = Mid$(strOutput, 2)
ExitHere:
DoCmd.Hourglass False
GetObjectList = strOutput
Exit Function
HandleErrors:
HandleErrors Err.Number, "GetObjectList"
Resume ExitHere
End Function
The main body of GetObjectList, once it's initialized local variables and set up the environment by turning on the hourglass cursor, consists of a Select Case statement with one case for each of the possible object types. For each type of object, the code uses the CurrentProject or CurrentData collections to iterate through the different objects.
Gathering Options
To emulate the built-in database window, frmDBC must know whether you've elected to display hidden and/or system objects. To gather this information, the GetObjectList function uses the Application.GetOption method. Based on the return values, the function sets the fShowHidden and fShowSystem variables; the function uses these variables to determine whether to include hidden and system objects in the output string. For more information on using Application.GetOption, see Appendix B.
Gathering Lists of Objects
In previous editions of this book, we used markedly different code to list the Jet objects (tables and queries) and the Access objects (in previous versions of Access, forms, reports, macros, and modules). From the users' point of view, there's no difference between Jet objects and Access objects, but from the developer's point of view, they're really separate types of objects. In this new version of the code, though, we can completely ignore these differences. That's because the CurrentProject and CurrentData collections bundle up all of these objects for you in identical ways.
Finding a Container
When you ask GetObjectList to produce a list of objects, you'll be looping through one of seven different collections. Although these collections each have a different object type, they each have identical methods and properties. Because of this, one of these collections can be treated like any other. Your first step then is to create a variable of type Object to refer to the correct collection. Following is the code from GetObjectList that performs this task:
Dim objCollection As Object
.
.
.
Select Case lngType
Case acTable
Set objCollection = CurrentData.AllTables
Case acQuery
Set objCollection = CurrentData.AllQueries
Case acForm
Set objCollection = CurrentProject.AllForms
Case acReport
Set objCollection = CurrentProject.AllReports
Case acDataAccessPage
Set objCollection = _
CurrentProject.AllDataAccessPages
Case acMacro
Set objCollection = CurrentProject.AllMacros
Case acModule
Set objCollection = CurrentProject.AllModules
End Select
Looping through the Collection
Once you've pointed the variable objCollection at a particular container, the code to loop through all the elements of the collection should look very familiar. It's a basic For...Each loop:
For Each aob In objCollection
...
Next aob
The code within the loop has to make decisions based on four factors:
- Is the object a system object?
- Is the object a hidden object?
- Is the object the current form?
- Is the object deleted?
Deciding Whether to Add an Object
For each particular object, you may or may not want to add it to the output string. If you have not requested that the function include system objects and the current object is a system object, you'll want to skip it. The same reasoning applies to hidden objects: you only want to show hidden objects if you've requested them.
fIsHidden = IsHidden(aob)
strName = aob.Name
fSystemObj = IsSystemObject(aob)
' Unless this is a system object and
' you're not showing system objects...
If (fSystemObj Imp fShowSystem) Then
' If the object isn't deleted and its hidden
' characteristics match those you're
' looking for...
If Not isDeleted(strName) And _
(fIsHidden Imp fShowHidden) Then
' later in the code:
strOutput & ";" & strName
End If
End If
Checking for System Objects
The first step in the preceding code was to determine whether or not the current table is a system object. To determine this you can call the IsSystemObject function:
Private Function IsSystemObject(aob As AccessObject) _
As Boolean
' Determine whether or not the specified object is
' an Access system object or not.
Const conSystemObject = &H80000000
Const conSystemObject2 = &H2
If (Left$(aob.Name, 4) = "USys") Or _
Left$(aob.Name, 4) = "~sq_" Then
IsSystemObject = True
Else
If (aob.Attributes And conSystemObject) = _
conSystemObject Then
IsSystemObject = True
Else
If (aob.Attributes And conSystemObject2) = _
conSystemObject2 Then
IsSystemObject = True
End If
End If
End If
End Function
The current object should be treated as a system object in three instances:
- If the name of the object is Usys followed by any text. This naming convention allows the user to create objects that Access will display, in the database container, only when the Show System Objects option is set to Yes.
- If the object is a query, built by Access for its own internal use with a name starting with "~sq_".
- If the object has particular bits in its Attribute property set.
The last test brings us into the murky waters of undocumented Access. We have no guarantee that our code for determining system objecthood from the Attributes property works. The Attributes property of the AccessObject object is hidden and undocumented. Presumably, it exists for the convenience of the Access designers, perhaps to help in implementing Wizards. It seems reasonable to assume that, like other Attributes properties elsewhere in the object models for Access, it consists of a series of bit constants. Exploring the Attributes properties for the default system tables, we found that MSysAccessObjects has an Attributes value of &H2, and the other system tables have an Attributes property of &H80000000. Why are there two different values? We don't know. We only know that Access treats all of these tables as system tables and that we need to do the same.
Tip
To discover hidden properties, you can open the object browser, right-click in it, and choose Show Hidden Members. This will reveal many methods, properties, and even whole objects that aren't documented. Of course, you use any of these at your own risk!
Checking for Hidden Objects
The code to check for hidden objects is similar to the code that checks for system objects, but it's simpler because there are no special cases, just a call to the GetHiddenAttribute method of the Application object:
Private Function IsHidden( _
aob As AccessObject) As Boolean
' Determine whether or not the specified object is
' hidden in the Access database window
If Application.GetHiddenAttribute( _
aob.Type, aob.Name) Then
IsHidden = True
End If
End Function
Note
One has to wonder about the GetHiddenAttribute (and corresponding SetHiddenAttribute) method. Why isn't Hidden a property of each AccessObject? We don't know—most likely, this was a feature added after the design review was done for the properties and methods of an AccessObject. In any case, this sort of nonconformist way of solving a problem should be a good lesson for you if you're creating your own objects with properties and methods—think hard about what properties your objects need before finalizing the design. This one was clearly tacked on after the fact and, because of that, feels rather odd.
Checking for Inclusion
You'll want to include the object in your list unless one of the following situations exists:
- You've asked not to include hidden objects, and this object is hidden.
- You've asked not to include system objects, and this object is a system object.
Let's consider the case of system objects first. You need to check whether to include an object, based on the fShowSystem setting and whether this particular object is a system object. These two conditions give you four possible outcomes, as shown in Table 6.14.
TABLE 6.14: Decision Table for System Object Inclusion
| SYSTEM OBJECT? | INCLUDE SYSTEM OBJECTS? | INCLUDE THIS OBJECT? |
|---|---|---|
| Yes | Yes | Yes |
| Yes | No | No |
| No | Yes | Yes |
| No | No | Yes |
As you can see in Table 6.14, you'll want to include the current object in the output string unless the current object is a system object and you've elected not to include system objects. You could build a complex logical expression to indicate this information to Access, but Access makes this a bit simpler by providing a single logical operator that works exactly as you need.
The IMP (implication) operator takes two values and returns a True value unless the first operand is True and the second is False. This exactly matches the truth table shown in Table 6.14. Given that the variable fSystemObj indicates whether or not the current table is a system object and the variable fShowSystem indicates whether or not you want to include system objects, you can use the expression
fSystemObj Imp fShowSystem
to know whether to exclude the table based on whether or not it's a system table. Therefore, to check both criteria for inclusion, you can use the following expression (which also checks for hidden objects in the same manner):
If (fSystemObj Imp fShowSystem) And _
And (fIsHidden Imp fShowHidden) Then
This expression returns a True value if all subexpressions return a True value.
The Current Form and Deleted Objects
Once you've determined that the current object matches the caller's interest in system and hidden objects, you have two new problems to handle:
- If this object is a form, should you list the current form?
- Is this object deleted? Access doesn't immediately remove deleted objects from the containers, and you won't want to display these objects in the list.
Set the constant adhcSkipThisForm in the module's declarations area to indicate whether you want to exclude the current form from the list. The following code fragment adds the current object name to the output string, depending on whether the item is deleted or hidden and if it's the current form:
If Not isDeleted(strName) And _
(fIsHidden Imp fShowHidden) Then
' If this isn't a form, just add it to
' the list. If it is, one more check:
' is this the CURRENT form? If so, and if
' the flag isn't set to include the current
' form, then skip it.
Select Case lngType
Case acForm
If Not (adhcSkipThisForm And _
(strName = Me.Name)) Then
strOutput = _
strOutput & ";" & strName
End If
Case Else
strOutput = _
strOutput & ";" & strName
End Select
End If
The isDeleted function takes a very low-tech approach to checking for deleted objects:
Private Function IsDeleted( _
ByVal strName As String) As Boolean
IsDeleted = (Left(strName, 7) = "~TMPCLP")
End Function
The function looks for object names that start with ~TMPCLP, which is how Access renames deleted objects.
Adding the Object
Once you've decided that a particular object is to be added to the list of tables, you'll want to place the Name property of the current object in the output string:
strOutput = strOutput & ";" & aob.Name
When the loop is done, the output string, strOutput, will contain one item for each acceptable object in the database.
Finishing It Up
Finally, once you've created a string containing all the object names for the selected type, GetObjectList returns the string to the calling procedure. The calling function, ListObjects, uses that string to fill the list box's RowSource property.
Using frmDBC in Your Own Applications
To use frmDBC in your own applications, just import it. Because all the code it requires to run is encapsulated in its module, you need nothing else. However, you might want to consider making various alterations to it. For example, you might want to add some columns or remove some of the tabs that appear as part of the tab control. In any case, we left the sample form simple so that you can modify it for your own needs.
Tip
You may want to allow the user to resize this form and resize or move all the controls on the form to match the current size. For more information on resizing objects on forms to match the current size of the form, see Chapter 8.
Summary
This chapter presented a broad introduction to programming data access using ADO. We've made attempts to bring our personal perspectives into this chapter, but a full understanding of this material requires far more depth than we can cover here. Because of the similarities between the object models in Access, Visual Basic, and the rest of Microsoft Office, you would be well served to spend as much time as possible digging in to this material: This is clearly the way future Microsoft products will be going.This chapter covered these major topics:
- Access' support for ADO with Jet
- Objects provided by ADO and ADOX
- Referring to objects
- Iterating through collections
- Using properties
- Jet data definition using ADO
- Working with ADO recordsets
- Schema recordsets
- Persisting recordsets
- Using Command objects
- Using collections supplied by CurrentData and CurrentProject
We purposefully neglected some key areas of ADO programming, mostly due to lack of space. Two major areas are:
- Events raised by ADO objects
- Handling disconnected, optimistic batch updates, including all the errors that can occur when you use this technique
For coverage of these and many other ADO-specific topics, you'll need to research books and articles specifically about ADO.
There's much, much more to know. As a start, Chapter 8 works extensively with forms and their recordsets. In addition, many of the chapters in Access 2000 Developer's Handbook, Volume 2, cover the use of ADO in file-server and client/server situations.