HandsOn Creating a Custom Recordset

1. Create a new Microsoft Office Access database or open the Acc2003_Chap16.mdb file from the book's downloadable files.

2. In the Database window, press Alt+F11 to switch to the Visual Basic Editor window.

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

4. In the module's Code window, type the Custom_Recordset procedure shown below.

Sub Custom_Recordset()

Dim rst As ADODB.Recordset Dim strFile As String Dim strPath As String Dim strFolder As String strPath = InputBox("Enter pathname, e.g., C:\My Folder") If Right(strPath, 1) <> "\" Then strPath = strPath & "\"

strFolder = strPath strFile = Dir(strPath & "*.*") If strFile = "" Then

MsgBox "This folder does not contain files." Exit Sub End If

Set rst = New ADODB.Recordset

' Create an empty recordset with 3 fields With rst

Set .ActiveConnection = Nothing .CursorLocation = adUseClient With .Fields

Creating and Manipulating Databases with ADO

.Append "Name", adVarChar, 255 .Append "Size", adDouble .Append "Modified", adDBTimeStamp

End With .Open

If strFile = "" Then Exit Do

' Add a new record to the recordset .AddNew Array("Name", "Size", "Modified"), _

Array(strFile, FileLen(strFolder & strFile), _ FileDateTime(strFolder & strFile)) strFile = Dir

Loop

.MoveFirst

' Print the contents of the recordset to the Immediate window

Do Until .EOF

Debug.Print !Name & vbTab & !Size & vbTab & !Modified .MoveNext

Loop

.Close

End With Set rst = Nothing End Sub

In the Custom_Recordset procedure shown above, we start by creating a Recordset object variable. To tell ADO that your recordset is not connected to any database, we set the ActiveConnection property of the Recordset object to Nothing. We also set the CursorLocation property to adUseClient to indicate that the processing will occur on the client machine as opposed to the database server. Next, we determine what columns the recordset should contain and add these columns to the recordset's Fields collection by using the Append method. Once the structure of your recordset is defined, you can call the Open method to actually open your custom recordset. Now you can populate the recordset with the data you want. We obtain the data by looping through the folder the user specified in the input box and read the information about each file. The VBA FileLen function is used to retrieve the size of a file in bytes. Another VBA function, FileDateTime, is used to retrieve the date and time a file was last modified. To retrieve the date and time separately, use the FileDateTime function as an argument of the DateValue or TimeValue functions.

Check the following statements in the Immediate window while stepping through the Custom_Recordset procedure:

? DateValue(FileDateTime(myFolder & myFile)) ? TimeValue(FileDateTime(myFolder & myFile))

Now that the recordset is fabricated and populated with the required data, you can display its contents in the Immediate window or send the output to another application. You can also save the recordset to a disk file as explained later in this chapter.

Part II

0 0

Post a comment