Data Access from Excel VBA - CopyFromRecordset Versus Looping (Page 4 of 4 )
When pulling data from a data source into Excel, you need to decide how you want to process the recordset. For example, if you need all of the fields and records, you have already been introduced to a simple method that does this: the CopyFromRecordset method places the results of a recordset for Excel Range objects in the range in an Excel worksheet. Sometimes, however, you bring in the results of a query but want only a few of the fields. You can accomplish this by looping through the records.
When dealing with ADO and DAO recordsets, there are several methods of record navigation. In most examples, I go to the first record in the recordset and move through the recordset until there are no additional records. You accomplish this by using theMOVEFIRSTmethod of a recordset to go to the first record, and then theMOVENEXTmethod to move through the recordset. There are several strategies to figure out when you have cycled through all of the records. When you move past the last record, the recordset’sEOFflag is set toTRUE. I suggest using aWhile...Wendloop. Assuming your recordset is a variable namedrs, you write theWhile...Wendloop like this:
rs.movefirst
While Not rs.eof
<Code to work with your Recordset>
rs.movenext
Wend
Since you have already been introduced toCopyFromRecordSet, let’s take a look at an example of where you would want to loop through a recordset. Consider a table of employee information that has 15 fields. Assuming that you need only three of the fields (Last Name, First Name, and Salary), you have two choices for how to accomplish this.
The first method is to simply write your query to pull in only the fields that you want using the criteria that you want. This sounds easy, but in some cases it might be difficult if you are not familiar with how to write SQL and if you do not have the appropriate access rights to make changes to the database. In that case, you would need to find out the field names, loop through the records, and test for criteria, if necessary. You could also pull the records by position.
For this example, assume that the fields that you want are called LName, FName, and EmpSalary and that you already have a variable,rs, which is the recordset object; a variable,x, which is an integer; and a variable,xlws, which is an Excel worksheet. You want those fields for people who have a salary of more than $60,000. This example is the same for both ADO and DAO.
x =1
rs.movefirst
While Not rs.eof
If rs.fields("EmpSalary").value > 60000 then
xlws.cells(x,1).value = rs.fields("LName").value
xlws.cells(x,2).value = rs.fields("FName").value
xlws.cells(x,3).value = rs.fields("EmpSalary").value
x = x + 1
End if
rs.movenext
Wend
The most important part of this Example is that you have thex = x + 1inside yourIfstatement. If not, you bring in only the records that you want, but you have blank spaces between the records. If you place thex = x + 1outside theIfstatement, each time a record is evaluated,xis incremented by one. In this procedure, the variablexdetermines the row to place the data. After looking at this example, you probably wonder how you get the titles of the fields on the Excel worksheet. When you pull the values by field name, this is very easy to do, either by simply writing the title that you want directly or by accessing the.NAMEproperty of the field object. However, when you perform aCopyFromRecordset, you may not know all of the fields.
Fortunately, there is a very easy method to cycle through the fields. Again, assume that you have a recordset (rs), you also have a variable calledfldthat is defined as a Field (same for ADO and DAO—ADODB.FieldorDAO.Field), and finally you have your Excel worksheet defined asxlws. Let’s put the titles in the first row and paste the recordset starting in row 2. Here is the code:
x = 1
For each fld in rs.Fields
xlws.cells(1,x).value = fld.name
x = x + 1
Next
xlws.range("A2").CopyFromRecordset rs
In general,CopyFromRecordset is quicker than cycling through the records; however, you do give up some control. If you do not see any benefits to moving through the records one at a time, I suggest writing your query to pull the records and fields that you need and then usingCopyFromRecordset. You may also have a situation when you want to use the same recordset for multiple purposes, and moving through the records is your best bet. In the previous example, for instance, we wanted to pull only the records where the salary was greater than $60,000. If you wanted to, you could use that same recordset to look at salaries less than $60,000 and place them in another location.
Please check back next week for the conclusion of this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter three of the book Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Check it out today at your favorite bookstore. Buy this book now.
|
|
