| With all those preliminaries out of the way, you can finally get down to the business of database programming. For the purposes of this chapter, database programming will consist of manipulating a recordset , which represents either the records in a table from an Access database or the records that result from a query. In ADO, you use the Recordset object to do all this. The first thing your procedures will always do is open a recordset. You do that by setting up a connection to a data source, which then gives you access to whatever database was specified when you created the data source. From there, you just specify the table you want to work with, or you set up a query. As you'll see, ADO handily enables you to do all of this with a single statement. Before all that, however, you must declare a variable as a Recordset type: Dim rs As Recordset With that done, you then Set the variable equal to a new Recordset object: Set rs = CreateObject("ADODB.Recordset") Since ADO isn't "built in" to Access (or any other program), its objects must be created explicitly by using VBA's CreateObject method. Now you're ready to open the Recordset object by invoking its Open method: Recordset .Open Source, Connection, CursorType, LockType, Options | Recordset | The Recordset object. | | Source | The source of the recordset, which can be a table name or an SQL SELECT statement (which I'll explain a bit later). | | Connection | The connection to use, which for our purposes is just the name of the data source that contains the data you want to work with. | | CursorType | A constant that specifies how the recordset is opened: | | | adOpenForwardOnly | This is a read-only, forward-scrolling cursor. Use this option for faster performance if you're just making a single pass through the records. This is the default. | | | adOpenDynamic | This is a dynamic cursor that enables you to insert and update records and to see changes made by other users. | | | adOpenKeyset | This is a keyset cursor that enables you to insert and update records and to see all changes made by other users, except record inserts . | | | adOpenStatic | This is a static copy of the records. You can insert and update records, but you can't see changes made by other users. | | LockType | A constant that specifies the locking characteristics of the new recordset: | | | adLockReadOnly | Prevents users from making changes to the records. This is the default. | | | adLockPessimistic | In a multiuser environment, the current record is locked as soon as you make changes to it. | | | adLockOptimistic | In a multiuser environment, the current recordset isn't locked until you run the Update method. | | | adLockOptimisticBatch | Implements batch optimistic updating (batch mode). You use this when you want to change multiple records and then update all of them at once. | | Options | A constant that specifies how the provider should interpret the Source value. See the VBA Help system for the various adCmd and adAsync constants that are available. | It's also worth noting that you can also open a recordset after first setting the following Recordset object properties: Recordset .Source ” A table name or SQL SELECT statement that specifies the source of the Recordset object. Recordset .ActiveConnection ” The connection to use for the Recordset object. Recordset .CursorType ” The cursor to use with the Recordset object. Recordset .LockType ” A constant that specifies the locking characteristics of the Recordset object. After setting these properties, you then run the Open method without specifying any parameters (see Listing 10.2, later in this chapter). Opening a Recordset Using a Table The easiest way to pen a Recordset object is to open a table that already exists within the data source. For example, the following statement opens the table named Employees in the Northwind data source: rs.Open "Employees", "Northwind" Listing 10.1 shows a complete procedure that shows you how to declare, open, and close a Recordset object.  | The code used in this chapter's examples can be found on my Web site at the following address: http://www.mcfedries.com/ABGVBA/Chaptr10.xls |
Listing 10.1. Opening a Recordset Object Using a Table Sub RecordsetOpenTable() Dim rs As Recordset ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' rs.Open "Employees", "Northwind" ' ' Close it ' rs.Close Set rs = Nothing End Sub This example doesn't do much, although it shows you how to fully handle a Recordset object. The first few statements declare the Recordset object, create it, and then open it. In this case, the code opens the Exployees table using the Northwind data source. We're not ready to do anything with the recordset just yet, so the last two statements close the Recordset and Set the variable to the keyword Nothing , which is a useful housekeeping chore that saves memory. Listing 10.2 shows the same code, only this time the Recordset object's Source and ActiveConnection properties are set before running the Open method. Listing 10.2. Opening a Recordset Object Using Properties Sub RecordsetOpenProperties() Dim rs As Recordset ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' With rs .Source = "Employees" .ActiveConnection = "Northwind" .Open End With ' ' Close it ' rs.Close Set rs = Nothing End Sub Opening a Recordset Using a SELECT String Rather than opening an entire table, you may prefer to open only a subset of a table. The easiest way to do that is to create a Structured Query Language (SQL) statement. This is the language that Access uses when you create a query. SQL is a complex bit of business, but you need only concern yourself with a small portion of it called the SELECT statement. The SELECT statement is used to create a recordset based on the table, fields, criteria, and other clauses specified in the statement. Here's a simplified syntax of the SELECT statement: SELECT [DISTINCT] field_names FROM table_name WHERE criteria ORDER BY field_names [DESC]; | SELECT | The SELECT statement always begins with the SELECT keyword. | | DISTINCT | This optional keyword specifies that you want only unique records (that is, no duplicates). | | field_names | If you only want certain fields to appear in the recordset, enter their names here, separated by commas. If you want all the fields, use *, instead. | | FROM table_name | This is the name of table that contains the data. | | WHERE criteria | This filters the data to give you only those records that match the specified criteria . | | ORDER BY field_names [DESC] | This sorts the results in ascending order based on the data in the fields specified by field_names (separated by commas, if you have more than one). Use the optional DESC keyword to sort the records in descending order. | For example, the following SELECT statement takes all of the fields from the Customers table, restricts the data to those records where the Country field contains "Sweden," and sorts the results using the data in the CompanyName field: SELECT * FROM Customers WHERE Country='Sweden' ORDER BY CompanyName; tip  | If you're new to SELECT statements, there's an easy way to avoid errors: use Access to create a temporary Select query in Access. When the resulting data is what you want, select the View, SQL View command to display the underlying SELECT statement. You can then copy this statement to your VBA code and delete the query. (One caution: change any double quotation marks (") to single quotes (') to avoid errors when using the SELECT statement within a VBA string variable.) |
As another example, the following SELECT statement takes just the ProductName and UnitPrice fields from the Products table and restricts the data to those records where the UnitPrice field is less than 20: SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice < 20; To use a SELECT statement in your VBA database code, either enter the SELECT string directly into the Recordset object's Open method as the Source value, or store it in a String variable and put the variable in the Open method, as shown in Listing 10.3. Listing 10.3. Opening a Recordset Object Using a SELECT Statement Sub RecordsetOpenSELECT() Dim rs As Recordset Dim strSELECT As String ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' strSELECT = "SELECT * FROM Customers WHERE Country='Sweden'" & _ "ORDER BY CompanyName;" rs.Open strSELECT, "Northwind", adOpenKeyset ' ' Close it ' rs.Close Set rs = Nothing End Sub | 