| As I mentioned near the beginning of the chapter, you normally use ADO from a program other than Access (or in Access when the database you want to work with isn't the current database). Most people work with table data from within Excel since the row-and-column layout of a worksheet fits well with the record-and-field layout of a table. To get data from a table into an Excel worksheet, you have three choices: -
Retrieving an individual field value. -
Retrieving one or more entire rows. -
Retrieving an entire recordset. Retrieving an Individual Field Value For individual field values, move to the record you want to work with and then assign the value of the field to the worksheet cell . For example, the following statement returns the value of the current record's Country field and stores it in cell A1 of the active worksheet: ActiveSheet.[A1] = rs("Country") Retrieving One or More Entire Rows To get full records, use the Recordset object's GetRows method: Recordset .GetRows Rows, Start, Fields | Recordset | The Recordset object you want to work with. | | Rows | The number of records you want to retrieve, starting from the current record. If you want to retrieve the rest of the records (that is, all the records from the current record to the end of the recordset), use the constant value adGetRowsRest . | | Start | Use this optional parameter to specify a bookmark as the starting point from which to retrieve the records. | | Fields | Use this optional parameter to specify the fields that are retrieved. Use a single field name or an array of field names . | The GetRows method returns the records in a two-dimensional array, where the first subscript is a number that represents the field (the first field is 0) and the second subscript represents the record number (where the first record is 0). Listing 10.10 shows an example. Listing 10.10. Retrieving Entire Records Into Excel Sub RetrievingEntireRecords() Dim rs As Recordset Dim strCriteria As String Dim recordArray As Variant ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' With rs .Source = "Customers" .ActiveConnection = "Northwind" .CursorType = adOpenKeyset .Open End With ' ' Head for Database Records worksheet ' Worksheets("Database Records").Activate With Worksheets("Database Records").[a1] ' ' Clear the sheet ' .CurrentRegion.Clear ' ' Read the data using GetRows ' recordArray = rs.GetRows(50) ' ' Run through the array and write the data to the worksheet ' For i = 0 To UBound(recordArray, 2) For j = 0 To UBound(recordArray, 1) .Offset(i + 1, j) = recordArray(j, i) Next j Next i ' ' Enter the field names in the first row and format the cells ' For j = 0 To rs.Fields.Count - 1 .Offset(0, j) = rs.Fields(j).Name .Offset(0, j).Font.Bold = True .Offset(0, j).EntireColumn.AutoFit Next j End With ' ' Close the recordset ' rs.Close Set rs = Nothing End Sub After opening the Customers table, this procedure performs a few Excel VBA chores, including activating the "Database Records" worksheet and clearing the sheet to remove any existing data. Then GetRows is used to retrieve the first 50 rows of the table. A For...Next loop runs through the two-dimensional array writing the data in the worksheet's rows and columns . Then another For...Next loop writes the column names on the top row and formats the cells for easier reading. Retrieving an Entire Recordset If you need to retrieve an entire recordset into a worksheet, one way to do it is to run GetRows(adGetRowsRest) from the first record and then use the technique in Listing 10.10 to write the data to the worksheet. However, Excel offers you an easier method ”the Range object's CopyFromRecordset method: Range .CopyFromRecordset( Data, MaxRows, MaxColumns ) | Range | A Range object that specifies the upper-left corner of the destination range. | | Data | The recordset containing the data you want to retrieve. | | MaxRows | The maximum number of records to retrieve. If you omit this optional parameter, Excel copies every record. | | MaxColumns | The maximum number of fields to retrieve. If you omit this optional parameter, Excel copies every field. | Here are a few notes to bear in mind when working with CopyFromRecordset : -
Excel begins the copying from the current record. If you want to retrieve every record, make sure you run the MoveFirst method to move to the first record. -
When the CopyFromRecordset method is done, the Recordset object's EOF property is True. -
CopyFromRecordset will fail if the Recordset object has a field that contains binary data (that is, if it's an OLE object field). Listing 10.11 shows the RetrieveCategories procedure that uses the CopyFromRecordset method. Listing 10.11. Retrieving an Entire Recordset Sub RetrieveCategories() Dim rs As Recordset Dim fld As Field Dim strSELECT As String, i As Integer ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' With rs .Source = "Categories" .ActiveConnection = "Northwind" .CursorType = adOpenKeyset .Open End With ' ' The strSELECT variable will hold the SQL SELECT statement ' that filters the Recordset to remove binary fields ' strSELECT = "SELECT " ' ' Run through the recordset fields ' For Each fld In rs.Fields ' ' Check for binary fields ' If fld.Type <> adBinary And fld.Type <> adLongVarBinary Then ' ' If it's not an OLE Object field, ' add it to the SELECT statement ' strSELECT = strSELECT & fld.Name & "," End If Next fld ' ' Remove the trailing comma ' strSELECT = Left(strSELECT, Len(strSELECT) - 1) ' ' Add the FROM clause ' strSELECT = strSELECT & " FROM Categories" ' ' Open the filtered recordset ' With rs .Close .Source = strSELECT .ActiveConnection = "Northwind" .CursorType = adOpenKeyset .Open End With ' ' Activate the Database Records worksheet ' Worksheets("Database Records").Activate With Worksheets("Database Records").[a1] ' ' Clear the sheet ' .CurrentRegion.Clear ' ' Get the entire recordset ' .Offset(1).CopyFromRecordset rs ' ' Enter the field names and format the cells ' For i = 0 To rs.Fields.Count - 1 .Offset(0, i) = rs.Fields(i).Name .Offset(0, i).Font.Bold = True .Offset(0, i).EntireColumn.AutoFit Next i End With ' ' Close and release the objects ' rs.Close Set rs = Nothing Set fld = Nothing End Sub The RetrieveCategories procedure opens the Categories table as the Recordset object. You want to make sure that you don't try to copy any OLE Object fields, so the procedure constructs a SQL SELECT statement that excludes any fields that contain binary data (OLE objects). The strSELECT variable holds the SELECT statement, so it's initialized to "SELECT ". Then a For...Next loop runs through each field in rs and looks for OLE Object fields (where the Type property is adBinary or adLongVarBinary ). If a field isn't an OLE Object type, its name (and a comma separator) is appended to the SELECT statement. Next, the trailing comma is removed and the FROM clause is concatenated to the SELECT statement. A new recordset is opened based on strSELECT , and then the CopyFromRecordset method retrieves the records. The Absolute Minimum This chapter showed you how to use the ActiveX Data Objects model to work with Access databases from other applications. You first learned how to set up the appropriate references and data sources. Remember: You need to set up the reference in each project where you want to use ADO programming techniques; you need only set up the data source for an Access database once. You also learned various methods for opening recordsets and getting recordset data. You then learned how to navigate a recordset, either by moving the cursor or by finding records. From there you learned how to edit records, add new records, and delete records. You completed the chapter by learning how to retrieve data into Excel. Here are a couple of related chapters to check out: -
For information on working with Excel's objects, see Chapter 8, "Programming Excel." -
I used the MsgBox function a few times in this chapter. See Chapter 12, "Interacting with the User," for the details on this function. |
|
