Convert an ADO Recordset to Text

This code snippet will show you how you can convert an ADO recordset to a delimited
text file in just a couple lines of code using the ADO GetString Method. You can easily export a recordset to a csv file using this method.

Original Author: unknown

Code

Dim rs As New ADODB.Recordset Dim fName As String, fNum As Integer rs.Open "Select * from myTable", db, adOpenKeyset, adLockReadOnly fName = "C:MyTestFile.csv" fNum = FreeFile


    Open fName For Output As fNum



    Do Until rs.EOF = True



        Print #fNum, rs.GetString(adClipString, 1,
",", vbCr)



    Loop



    rsA.Close

    Close #fNum

______________________________________________________________________

GetString Method

Returns the Recordset as a string.

Syntax

Variant = recordset.GetString(class="synParam" onclick="showTip(this)" href>StringFormat, onclick="showTip(this)" href>NumRows, ColumnDelimiter, class="synParam" onclick="showTip(this)" href>RowDelimiter, onclick="showTip(this)" href>NullExpr)

style="VISIBILITY: hidden; OVERFLOW: visible; POSITION: absolute">

Return Value

Returns the Recordset as a string-valued Variant (BSTR).

Parameters


 StringFormat 

 A StringFormatEnum value that specifies how the Recordset
 should be converted to a string. The RowDelimiter, ColumnDelimiter, and NullExpr
 parameters are used only with a StringFormat of adClipString. 

 NumRows 

 Optional. The number of rows to be converted in the Recordset. If NumRows is
 not specified, or if it is greater than the total number of rows in the Recordset,
 then all the rows in the Recordset are converted. 

 ColumnDelimiter 

 Optional. A delimiter used between columns, if specified, otherwise the TAB character. 

 RowDelimiter 

 Optional. A delimiter used between rows, if specified, otherwise the CARRIAGE RETURN
 character. 

 NullExpr 

 Optional. An expression used in place of a null value, if specified, otherwise the empty
 string. 


Remarks

Row data, but no schema data, is saved to the string. Therefore, a Recordset
cannot be reopened using this string.

This method is equivalent to the RDO GetClipString method.

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 123 times

Comments

No comments have been added for this post.

You must be logged in to make a comment.