Export Recordset to Excel

I am trying to export a Adodb Recordset to excell through a VB 6.0 application. I can do that by For Loop. But the recordset contains 100 columns with 200000 Rows. So it is consuming huge time to complete the t开发者_开发问答usk.At times it is getting hanged. Is there a fast way to achieve the same? Thankx in advance

There are some ways to dramatically improve the performance of ADODB.Recordset objects. One of the best tips I've used to speed things up is to create explicit field objects in your loop:

Dim rs As New ADODB.Recordset
Dim fldLastName As ADODB.Field, fldFirstName As ADODB.Field

rs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText

Set fldLastName = rs.Fields("au_lname")
Set fldFirstName = rs.Fields("au_fname")

Do Until rs.EOF
    csvOutput = csvOutput & fldLastName & "," & fldFirstName & vbCrLf
    rs.MoveNext
Loop
rs.Close

This can really speed things up because you avoid late binding in referring to the Recordset.Field.Value items. Give it a try and you should see a huge improvement in performance. I have worked with large recordsets like the one you described and the performance has been made tolerable using this technique.

By the way, there is a good article on MSDN that has some other tips that might help improve the performance of your ADODB code here - ADO Performance Best Practices. The article is quite old now but I think it all still applies after reading it again for the first time in years.

Some suggestions:

The Excel object model has a CopyFromRecordset method.

The Recordset object has a GetRows method to return an array which you can 'transpose' into an Excel Range object's Value property ('transpose' = columns to rows and vice versa -- Excel has a Transpose worksheet function that can be invoked via its object model).

Note that before Excel 2007, worksheets were limited to 65,536 rows and you may find that the abovementioned Excel methods are similarly limited.

The Recordset object has a GetString method to write all rows at once to a string (no need to loop).

You may be able bypass the recordset completely and use Access SQL (ACE, Jet, whatever) to write the data directly to Excel e.g.

SELECT *
  INTO [Excel 8.0;DATABASE=C:\MyNewWorkbook.xls].MyNewTable 
  FROM [ODBC;Driver={SQL Server};Server=MyServer;Database=MyDB;UID=Admin;PWD=***].MyDBTable;