Using CopyFromRecordset This chapter focuses on using the ADO and DAO object libraries to bring data from a database into an Excel workbook. Most of the material concerns fine-tuned activities, such as using VBA code to find the exact location that data needs to occupy on a worksheet. Often, though, you're less interested in picking and choosing among records and putting a field value in a particular location, and more interested in copying a lot of data very rapidly. You could use an external data range, if you wanted to, as described in Chapters 4, "Importing Data: An Overview," and 5, "Using Microsoft Query." Refreshing an existing data source is a speedy process. But you usually set up an external data range manually, starting with Data, Import External Data. (You can set one up using VBA, of course. To see how this is accomplished, just turn on the macro recorder before you start the process.) No matter how you set up an external data range, a named range is needed. You might not want to add a named range to the workbook. In that case, consider using Excel's CopyFromRecordset method. Note that CopyFromRecordset isn't part of the DAO or ADO object libraries. It's a method that belongs to Excel worksheet ranges. Nevertheless, you must establish a reference to either ADO or DAO in your code because the principal argument used by CopyFromRecordset is the recordset object to be copied into the worksheet. Here's an example that uses a DAO recordset. (An example using an ADO recordset follows.) Of course, before you could run it, you would need to establish a reference to a DAO library using Tools, References from the VBE.  For more information on establishing library references see "Connecting Using ADO," p. 194.
Sub CopyFromRecordsetWithDAO() Dim dbNorthWind As DAO.Database Dim tdfOrders As DAO.TableDef Dim rsOrders As DAO.Recordset Dim i As Integer Set dbNorthWind = OpenDatabase("C:\Documents and Settings" & _ "\Owner\My Documents\Northwind.mdb") Set tdfOrders = dbNorthWind.TableDefs("Orders") Set rsOrders = tdfOrders.OpenRecordset(dbOpenTable) With ActiveSheet For i = 0 To rsOrders.Fields.Count - 1 .Cells(1, i + 1) = rsOrders.Fields(i).Name Next i .Cells(2, 1).CopyFromRecordset rsOrders End With End Sub
The code establishes a recordset, rsOrders, that represents the Orders table in the Access example database, Northwind.mdb. When it executes the CopyFromRecordset method, the contents of that table are copied to the active worksheet. The copy occurs very quickly. In this example, 830 records with 14 fields each were copied to a worksheet in less than one second, using a Pentium 4 1.80GHz processor. Notice that the code loops through the list of fields that belong to the recordset, and writes the name of each field to the first row of the active worksheet. The CopyFromRecordset method does not supply field names. In contrast, if you establish an external data range, you get the field names by default. Because the first row contains the field names, the recordset's records and fields are copied to the active worksheet beginning in row 2, column A. The following code passes an ADO recordset to the CopyFromRecordset method. Notice the use of ADO's Recordset object, and also its use of a SQL Server file instead of a Jet database. Sub CopyFromRecordsetWithADO() Dim rsOrders As ADODB.Recordset Dim i As Integer Dim cnnConnectSpec As String cnnConnectSpec = "Provider=SQLOLEDB.1;Data Source=(local);" & _ "Initial Catalog=NorthwindCS;Integrated Security=SSPI" Set rsOrders = New ADODB.Recordset rsOrders.Open "Orders", cnnConnectSpec, adOpenStatic, _ adLockReadOnly, adCmdTable With ActiveSheet For i = 0 To rsOrders.Fields.Count - 1 .Cells(1, i + 1) = rsOrders.Fields(i).Name Next i .Cells(2, 1).CopyFromRecordset rsOrders End With End Sub
The logic of returning field names and values to the worksheet is identical whether you're using DAO or ADO. Notice the string that contains the connection information for the ADO recordset. It specifies SQLOLEDB.1 as the provider and (local) as the data source. In a networked environment, you would use the name of the server storing the catalog as the data source. The use of (local) indicates that a server has been installed on the local workstation. Microsoft has offered a desktop version of SQL Server since Office 2000, termed the Microsoft Data Engine, or MSDE. (SQL Server refers to this version as the SQL Server Desktop Engine.) The steps to install it depend on the version that you're running. In Office 2000 Premium, for example, you install Microsoft Office Server Extensions from Disk 3 of the Office 2000 product. In Office 2003, one way is to locate a folder named MSDE2000 on the installation disks. That folder contains an executable installation file; double-click its icon to run it. With the server installed, you can use the SQL Server compatible version of the Northwind sample database. Again, getting to it depends on the Office version you're running. In Access 2000, you use File, Open and browse to the Samples subfolder within the Office folder. In Access 2003, choose Sample Databases from the Help menu. If all you're doing is bringing a set of data based on a query or on a table from a database to an Excel worksheet, the CopyFromRecordset method might be all you need. When the data is on the worksheet, you can use the full array of Excel tools, such as charts and pivot tables and worksheet functions, to analyze the data. If you need to return the data in a specific format, however, you'll need recourse to the techniques discussed in the remainder of this chapter. | 