HandsOn Converting the Recordset to a String

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the GetRecords_AsString procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub GetRecords_AsString()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim varRst As Variant Dim fso As Object Dim myFile As Object

Set conn = CurrentProject.Connection

Set rst = New ADODB.Recordset rst.Open "SELECT EmployeeId, " & _

"LastName & "", "" & FirstName as FullName " & _ "FROM Employees", _

conn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not rst.EOF Then

' Return all rows as a formatted string with ' columns delimited by Tabs, and rows ' delimited by carriage returns varRst = rst.GetString(adClipString, , vbTab, vbCrLf) Debug.Print varRst End If

' save the recordset string to a text file

Set fso = CreateObject("Scripting.FileSystemObject")

Set myFile = fso.CreateTextFile(CurrentProject.Path & _ "\RstString.txt", True) myFile.WriteLine varRst myFile.Close

Set fso = Nothing rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

The GetRecords_AsString procedure demonstrates how you can transform a recordset into a tab-delimited list of values using the Recordset object's GetString method. You can use any characters you want to separate columns and rows. This procedure uses the following statement to convert a recordset to a string:

varRst = rst.GetString(adClipString, , vbTab, vbCrLf)

Part II

Notice that the second argument is omitted. This indicates that we want to obtain all the records. To convert only three records to a string, you could write the following line of code:

varRst = rst.GetString(adClipString, 3, vbTab, vbCrLf)

The vbTab and vbCrLf arguments are VBA constants that denote the Tab and carriage return characters.

Because adClipString, vbTab, and vbCrLf are default values for the GetString method's arguments, you can skip them altogether. Therefore, to put all of the records in this recordset into a string, you can simply use the GetString method without arguments, like this:

varRst = rst.GetString

Sometimes you may want to save your recordset string to a file. To gain access to a computer's file system, the procedure uses the CreateObject function to access the FileSystemObject from the Microsoft Scripting Runtime Library. Using the CreateTextFile method of this object, you can easily create a File object. Notice that the second argument of the CreateTextFile method (True) indicates that the file should be overwritten if it already exists. Once you have defined your file, you can use the WriteLine method of the File object to write the text to the file. In this example, your text is the variable holding the contents of a recordset converted to a string.

0 0

Post a comment