Convert an ADO Recordset to Text

Description

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.

More Info

Submitted On
By	N/A
Level	Beginner
User Rating	4.7 (47 globes from 10 users)
Compatibility	VB 6.0
Category	Databases/ Data Access/ DAO/ ADO
World	Visual Basic
Archive File

Source 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(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

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.