Opening a Record Object Referencing an Open Recordset Object

Introduction

In the previous tutorial, Using ADO’s Record Object With URLs, it was shown in considerable detail how a record object may be leveraged to access files and folders on a web site. This tutorial shows how a record object may be opened by referencing an already open recordset object. As you may see in the following picture, the record and recordset objects are interrelated in the ADO Model.

Opening a record object

In order to use the record and recordset objects you need the OLEDB providers. In fact, since the Record object has to use an already open recordset object, the Recordset object has to be opened first. The record object represents a single row in the open recordset object. The record object's strength is its ability to navigate hierarchical structures of file and folders, while the recordset must use an OLE DB provider suitable for this purpose.

The most appropriate OLEDB provider is the OLEDB Provider for Internet Publishing (MSDAIPP). MSDAIPP stands for Microsoft Data Access Component Internet Publishing Provider. The file location is C: Program Files Common Files System OleDB. If you have Microsoft Office 2000 and above, or your operating system is Windows 2000 or Office XP Professional, you should have the file in your system as shown here.

The MSDAIPP can access folders and files on web servers if the following items are supported:

• FrontPage Server Extensions
• Web Extender Client
• Web Distributed Authoring and Versioning
  (WebDAV or HTTP-DAV) protocol extension for HTTP

In order to use the above provider, you need to provide a valid URL address for the file you want to open. Here we will be using some intranet (local web server) virtual folders as shown in the following pictures.

This picture shows the virtual site that is used in opening the recordset.

Opening a Recordset by Providing an URL

Create a form RstRecord in your MDB application. This MDB database has no tables; we are just using the Form objects. Add a button, and to the click event of the command button, paste this code. It shows how easy it is to open a recordset object providing an URL.

Private Sub Command0_Click()

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

'rst.Open "", "URL=http://hodentek/"

rst.Open "Hodentek", "URL=http://hodentek/"

MsgBox ("Recordset is open:" & rst.State)

rst.Close

MsgBox ("Recordset is closed:" & rst.State)

End Sub

You may be wondering, what about the provider information needed to open the recordset object? If the IIS (Web Server) is correctly installed, this provider is automatically used. Add the following code after the rst.open but before the rst is set to a nothing statement.

Private Sub Command0_Click()

 Dim rst As ADODB.Recordset

 Set rst = New ADODB.Recordset

 rst.Open "", "URL=http://hodentek/"

 Text1.SetFocus

 Text1.Text = rst.ActiveConnection

 Text3.SetFocus

 Text3.Text = rst.Source

 Text5.SetFocus

 Text5.Text = rst.CursorType

 rst.Close

 Set rst = Nothing

 End Sub

When the form is open and the button is clicked the following details are displayed. The other form’s format details are: Text1 control’s Scroll Bars property is set to Vertical and the Can Grow property is set to “Yes.”

You may be wondering what the fields collection consists of. The first item is AccXML.htm - an alphabetically sorted HTM file, followed by ‘http://hodentek’ and ‘http://hodentek/AccXML.htm’.

Opening the Record Object after Opening the Recordset Object

The record object has a number of properties and methods as shown in this extract from the Object Browser. These may be interrogated once the record is open.

The Record.Open () method has the following syntax as shown in the next picture. Not only it is possible to display the records, but you can also create files and folders.

Use the following code in another form’s button click event, or another button on the same form.

Private Sub Command0_Click()

‘Declare a recordset object

Dim rst As ADODB.Recordset

‘Declare a record object

Dim rec As ADODB.Record

‘instantiate the recordset

Set rst = New ADODB.Recordset

‘open the recordset

rst.Open "", "URL=http://hodentek/Atlas Website"

‘instantiate the record [Recordset is Open]

Set rec = New ADODB.Record

‘open the record

rec.Open rst

‘--------Interrogate Properties-----

Text1.SetFocus

Text1.Text = rec.ActiveConnection

Text5.SetFocus

Text5.Text = rec.Mode

Text7.SetFocus

Text7.Text = rec.ParentURL

Text9.SetFocus

Text9.Text = rec.RecordType

‘---end interrogation--

Set rst = Nothing

End Sub 

The Mode and RecordType are returned as Enumerated constants, a basic feature of ADO. Mode: 1 refers to the constant adModeRead, the default, read only for the record open. The RecordType: 0 refers to the constant adSimpleRecord which means there are no child records. The ParentURL property is also read only. How many fields are there in this record and what are they?

To answer these questions you need to know how many fields there are in the record. This is easily done by including a calling of the the rec.Count property. Once you know this, you find the items collection in which each item is indexed and has a name and value by inserting the following code snippet before you close the record (between Text9.text and Set rst=nothing).

MsgBox (rec.Fields.Count)

For i = 0 To rec.Fields.Count – 1  

Debug.Print rec.Fields (i).Name & " : " & rec.Fields (i).Value & vbCrLf

Next i 

The Debug.Print statement copied from the immediate window is as follows:

RESOURCE_PARSENAME : Default.aspx

RESOURCE_PARENTNAME : http://hodentek/AtlasWebSite

RESOURCE_ABSOLUTEPARSENAME : http://hodentek/AtlasWebSite/Default.aspx

RESOURCE_ISHIDDEN : 

RESOURCE_ISREADONLY : 

RESOURCE_CONTENTTYPE : 

RESOURCE_CONTENTCLASS : 

RESOURCE_CONTENTLANGUAGE : 

RESOURCE_CREATIONTIME : 

RESOURCE_LASTACCESSTIME : 

RESOURCE_LASTWRITETIME : 3/21/2006 4:06:42 PM

RESOURCE_STREAMSIZE : 924p

RESOURCE_ISCOLLECTION : False

RESOURCE_ISSTRUCTUREDDOCUMENT : 

DEFAULT_DOCUMENT : http://hodentek/Default.htm;http://hodentek/

Default.asp;http://hodentek/index.htm;http://hodentek/iisstart.asp

RESOURCE_DISPLAYNAME : Default.aspx

RESOURCE_ISROOT : False

RESOURCE_ISMARKEDFOROFFLINE : 

RESOURCE_METATAGS : 

RESOURCE_LISTBASETYPE : 

DAV:name : Default.aspx

DAV:parentname : http://hodentek/AtlasWebSite

DAV:displayname : 

Default.aspx

DAV:isroot : False

DAV:getlastmodified : 3/21/2006 4:06:42 PM

DAV:getcontentlength : 924

DAV:iscollection : False

DAV:defaultdocument : http://hodentek/Default.htm;http://hodentek/
Default.asp;http://hodentek/index.htm;http://hodentek/iisstart.asp

These are standard fields returned by the OLE DB provider, in this case the Internet Publishing (MSDAIPP) provider. This list also contains the item RESOURCE_STREAMSIZE: 924, the size of the resource which may be retrieved using the Stream object.

RecordType and Mode Properties

The reader is referred to the previous article on the record object. The Mode property can be set before the record and is open to different values, as the following picture shows. When the record is open, it is read only. This is more like recordset’s LockType property.

Summary

The tutorial shows how to open a record using an already open recordset object. Only a few basic properties have been covered in this tutorial. The ability to read and write are not covered. The record object can also be used to Copy, Move, and Delete records. These will be covered by concrete examples in a future tutorial.