The Record object in ADO 2.5 expands the capability of the ADO Object model. If you want to learn how you can use this in your own applications, keep reading.
When ADO 2.5 was introduced, two new items to the ADO Object model were added. While the earlier versions of ADO addressed the operations dealing with relational databases, new necessities drove the enlargement of the applicability of ADO to non-relational, hierarchical data like those in web sites and folder/file systems. Record and Stream objects were born out of these necessities. These additions considerably extended ADO's applicability beyond the well known databases such as MS SQL Server, Oracle, and so forth and provided access for looking into web site directories, file structures with directories, nested subdirectories, and file systems, exchange mail folders (you need OLE DB provider for Microsoft Exchange Server for this), and so on.
This tutorial looks at the Record object and explores how it can be leveraged in an application where you want to access folders in your web server's URL. The next picture shows the position of the Record Object in the ADO hierarchy.
In order to use these objects you need the OLE DB providers. The most appropriate OLE DB provider for opening Record or Stream objects is the OLE DB Provider for Internet Publishing (MSDAIPP). MSDAIPP stands for Microsoft Data Access Component Internet Publishing Provider. The version on this computer is MSDAIPP.dll ( 11.0.5510.0). The file location is C: Program FilesCommon Files SystemOleDB. 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. The MSDAIPP can access folders and files on web servers if the following are supported:
FrontPage Server Extensions
Web Extender Client
Web Distributed Authoring and Versioning (WebDAV or HTTP-DAV) protocol extension for HTTP
Microsoft Summarized Documentation for ADO 2.8
The ADO 2.8 version will be used in the examples for this tutorial. Here is the Microsoft Document summary for the Record object's various supported properties and methods of this version. This object does not have any Events associated with it. The list below is taken from the MSDN site.
In this tutorial we will be looking at two of the URLs whose root folders are as shown in the next picture. The URLs for these are http://localhost/TestingService/ and http://localhost/ Factory/. As shown, there are files associated with these folders. For your examples make sure you have an IIS server and a couple of files and folders.
The folders, and files contained in the folders, are accessed using ADO calling upon the MSDAIPP provider discussed earlier. In general, folders and files have different attributes. The folder is a collection of other folder (non-leaf node) objects and may contain file (leaf node) objects. The Record object can look into hierarchies (folder/..../files) and can distinguish between files and folders through the attributes which are not necessarily similar. The next picture shows the properties of a folder and the one after that shows properties of a file. While some properties are common to both of them, some are not. If the property attributes are the same it is termed rectangular data.
The properties common to the folders and files are: Location, Size, Size on disk, and Attributes. While the Record object can look at the details of the folder as we shall see in this tutorial, the contents of a file need the other object, the Stream object. The Stream object will be described in a future tutorial.
Create an MS Access project, add a form in design, and add a Command button and a text box. For using ADO 2.8 you need to add a reference to the library as shown in the next picture. The details of this can be found at the link Connecting-to-Microsoft-Access-with-ADO.
This same link also shows how you arrive at the connection string for the ADO connection that you are planning to make. In the present case, we need to use the MSDAIPP provider. The next picture shows some relevant pictures of the data link file url.udl from which the connection string was extracted by opening it in a text editor. The next picture shows that you need to choose the MSDAIPP provider.
The next picture shows the data source and authentication. The file has been set up for anonymous logon. At first we will be looking at the file in a web site folder. You may also verify whether the connection is working or not by testing it.
Type in (or Copy) the following code to the click event of the Command button. Open the file and click the button; you will see the display shown in the next picture. The text formatting was applied in the design mode.
Private Sub Command0_Click()
'Opening a Record object given the URL
Dim con As New ADODB.Connection
Dim rec As New ADODB.Record
Dim constr As String
'ConnectionString from URL.udl file's text
constr = "Provider=MSDAIPP.DSO; Data Source=http://hodentek/
TestingService/HelloOra.asmx;" & _
"Bind Flags=0;Mode=Read;Lock Owner='';Url Encoding=10;User ID=jay;" & _
"Password=;Ignore Cached Data=False; Cache Aggressively=False;" & _
"Treat As Offline=False; Mark For Offline=0;" & _
"Protocol Provider={00000000-0000-0000-0000-000000000000};" & _
"Flush WinInet Password Cache=False; User Agent string="";" & _
"Custom Http Headers="";Use Cached DAV lock Token=False;" & _
"Bypass LIS for Invoke command=False;
Dump XML response form command execution=False;" & _
"Suppress resource columns in command result=False;" & _
"Show thicket files for WEC server=False;
Redirect WinHttp to XmlHttp=False"
con.Open constr
rec.ActiveConnection = con
rec.Open
'All the properties will be stuffed into this string
Dim strPearl As String
strPearl = ""
strPearl = strPearl + "File explored is: http://hodentek/
TestingService/" & _
"HelloOra.asmx " & vbCrLf & vbCrLf
strPearl = strPearl + "Record's parent URL is: " & rec.ParentURL & _
vbCrLf & vbCrLf
strPearl = strPearl + "Now the Record's State (1 is adStateopen," & _
"0 is adStateClosed, 4 is adStateExecuting) is: " & rec.State &
vbCrLf & vbCrLf
strPearl = strPearl + "Record's Mode is (1 is adModeRead [default]): "
& _
rec.Mode & vbCrLf & vbCrLf
strPearl = strPearl + "Record's RecordType (0 is adSimpleRecord
(Leaf-node)," & _
"1 is adCollectionRecord (non-Leaf) is: " & rec.RecordType & vbCrLf &
vbCrLf
strPearl = strPearl + "Record's Source is: " & rec.Source & vbCrLf & vbCrLf
strPearl = strPearl + "Record's Fields(0) item: " & rec.Fields(0) &
vbCrLf & vbCrLf
strPearl = strPearl + "Record's Fields(1) item: " & rec.Fields(1) &
vbCrLf & vbCrLf
strPearl = strPearl + "Record's Fields(2) item: " & rec.Fields(2) &
vbCrLf & vbCrLf
strPearl = strPearl + "Number of properties for this record are : " & _
rec.Properties.Count & vbCrLf & vbCrLf
rec.Close
strPearl = strPearl + "Now the Record's State (1 is open, 0 is closed)
is: " & _
rec.State & vbCrLf
txtFile.SetFocus
txtFile.Text = strPearl
con.Close
End Sub
Once the connection string is correctly set, the derivation of the properties, or the usage of the methods is quite simple. For most of the properties the enumerated constants are used. The enumerated constants and their values are also shown for some of the properties in the above picture. For example, RecordType=0 means it is a file, whereas RecordType=1 would imply a folder. The properties explored are highlighted in the code shown above. One of the properties which distinguishes a file from a folder is the RecordType. The RecordMode property used with records is very similar to the LockType property used with recordsets. The Fields collection refers to the various parts of the URL using an index as shown in the code. In the above example, rec.Fields(0) is the HelloOra.asmx file. In order to read the content of this field you would require a Stream object.
In this case we will be looking at the web folder and its contents. For this we create a form in MS Access, and place a textbox, a list box and a button. Set the properties of the list box as described in the article in the link so that the RowSource type of the ListBox is ValueList. Type in or copy the following code to the click event of the button. When the form is displayed it should look like the next picture. Some design time formatting has been applied to the controls.
Private Sub Command0_Click()
Dim con As New ADODB.Connection
Dim rec As New ADODB.Record
Dim constr As String
'This is the connection string generated by the url.udl file
constr = "Provider=MSDAIPP.DSO; Data Source=http://hodentek/Factory;" & _
"Bind Flags=0;Mode=Read;Lock Owner='';Url Encoding=10;User ID=jay;" & _
"Password=;Ignore Cached Data=False; Cache Aggressively=False;" & _
"Treat As Offline=False; Mark For Offline=0;" & _
"Protocol Provider={00000000-0000-0000-0000-000000000000};" & _
"Flush WinInet Password Cache=False; User Agent string="";" & _
"Custom Http Headers="";Use Cached DAV lock Token=False;" & _
"Bypass LIS for Invoke command=False; Dump XML response form command
execution=False;" & _
"Suppress resource columns in command result=False;" & _
"Show thicket files for WEC server=False; Redirect WinHttp to
XmlHttp=False"
con.Open constr
rec.ActiveConnection = con
rec.Open
Dim strg As String
strg = ""
strg = strg + "The folder explored is: http://hodentek/Factory" &
vbCrLf & vbCrLf
strg = strg + "Record's parent URL is: " & rec.ParentURL & vbCrLf &
vbCrLf
strg = strg + "Record's State (1 is open, 0 is closed) is: " &
rec.State & vbCrLf & vbCrLf
strg = strg + "Record's Mode is: " & rec.Mode & vbCrLf & vbCrLf
strg = strg + "Record's RecordType (adSimpleRecord is 0;
adCollectionRecord is 1;" & _
"adStructDoc is 2) is: " & rec.RecordType & vbCrLf & vbCrLf
strg = strg + "Record's Source is: " & rec.Source & vbCrLf & vbCrLf
strg = strg + "Record's Fields(0) item: " & rec.Fields(0) & vbCrLf &
vbCrLf
strg = strg + "Record's Fields(1) item: " & rec.Fields(1) & vbCrLf &
vbCrLf
strg = strg + "Record's Fields(2) item: " & rec.Fields(2) & vbCrLf &
vbCrLf
strg = strg + "Number of properties for this record are : " &
rec.Properties.Count & vbCrLf & vbCrLf
strg = strg + "Size of GetChildren(0) is : " &
rec.GetChildren(0).ActualSize & vbCrLf & vbCrLf
Set chilun = rec.GetChildren
Do Until chilun.EOF
List3.AddItem chilun(0)
chilun.MoveNext
Loop
rec.Close strg = strg + ("Record's State (1 is open, 0 is closed)
is: " & rec.State)
Text1.SetFocus
Text1.Text = strg
con.Close End Sub
Some of the properties and methods are highlighted in the code. The important point to note is that the RecordType is 1, which refers to a collection (the sub-folders and files contained therein). The fields collection uses an index (0 based) to point to the field items.
The GetChildren() method accesses the subfolders and files in the directory, which is in this case Factory. The contents of the folder are shown in the list box with one sub-folder and several files. In order to look at the content of the individual aspx pages, you would need a Stream object.
The connection string derived from the Url.udl file is very long, but it is sufficient to use a shorter string such as the code shown in the next paragraph.
Private Sub Command0_Click()
Dim con As New ADODB.Connection
Dim rec As New ADODB.Record
Dim constr As String
constr = "Provider=MSDAIPP.DSO;" & _
"Data Source=http://hodentek/TestingService/HelloOra.asmx"
con.Open constr
rec.ActiveConnection = con
rec.Open
MsgBox ("It is open")
MsgBox (rec.ParentURL)
End Sub
A complete list of different ways to open the record, recordset, and connection objects is available here.
The Record object also opens with implicit reference to the MSDAIPP when the connection string is not explicitly provided, as in the following code:
Private Sub Command1_Click()
Dim rec As New ADODB.Record
rec.Open "", "URL=http://localhost/TestingService"
Dim feld As ADODB.Field
For Each feld In rec.Fields
Debug.Print feld.Name & "/" & feld.Value
Next
End Sub
The Field Name and Field Value from the above code will be as shown in the next paragraph (copied from the immediate window). In this case, the resource is the folder with a number of items referring to the default site. The DAV: items refer to the WebDAV which stands for Web Distributed Authoring and Versioning attributes.
The URL is only one way of opening a Record object where the record source is an URL. A Record object can also be opened where the source may take these different forms: a Command object, a SQL statement, a table name, or an open recordset object. These different ways are not discussed. Also not discussed are the Copy and Delete methods shown earlier. The Record object and the Recordset object, though not the same, are related. Since both of them have fields collection, one has to be careful when using these objects. The code in this tutorial may be used as is, making changes to the available URL.
Microsoft Access 
/ 8 
ADO`s Stream Object
