April 14, 2011, 9:23 p.m.
posted by dante
Working with Database Records: Opening a Recordset
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.
Recordset.Open Source, Connection, CursorType, LockType, Options
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.
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.
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];
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;
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.
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