Using ADO Record to Create and Navigate Intranet Files and Folders

Introduction

The expanded capability of ADO to access folders and files on the web site came with version 2.5. In an earlier article ADO's capability to look into the folder structure on a web site was discussed. The Record object can confer lot more power to the developer than merely exploring the folder/file structure. You can create folders and files. You can also navigate the folder/file structure as well as copy, move and delete objects. This is quite a feat for an object with a very small object model.

This tutorial begins with creating a predetermined folder structure, builds files into one of the folders, and then moves those files to another folder which is created empty. The tutorial also shows how you can navigate the created folder/file structure.

Overview of the objective of this tutorial

The following folder file structure will be created in an intranet site using the ADODB's record object.

• DevHome
• ASPFree
  • Test.htm
  • Test.xls
  • Test.bmp
  • Test.aspx
• DevShed
• DevArticles

At first the three empty directories will be created in the DevHome folder. Following this, the ASPFree folder will be populated with the files. The four files in the ASPFree folder will be copied over to the DevShed folder at the conclusion of the tutorial.

Establish a library reference to ADO 2.8

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 for how to do this can be found in the article titled Connecting to Microsoft Access with ADO. MS Access is being used because of the easy access to Microsoft Visual Basic Editor and for no other reason. Most of the code is being written to the click event of a button.

Creating folders

The following picture shows the intranet site, the default website which can be accessed by its URL, http://hodentek. You will notice that there is no folder by the name DevHome.

Add a form in design and add the code you will see below to the click event of the button. You will find the comments added at appropriate places. This code creates a DevHome folder in the intranet site. Sine it is a folder the adCreateCollection enum constant has been used. If the directory exists, it is overwritten. The over writing does not affect the files contained in the folder. This was later verified.

The options can be added as shown, and in fact the code completion utility helps you in doing so. The open method of the record takes several arguments which are shown in the following picture. After typing in record.open just put an empty space, wait for the drop down to show up and then start typing in the values or picking from ensuing drop-down for each argument.

record.Open (source[can be an url],
ActiveConnection,
Mode,
CreateOptions,
Options,
Username,
Password)

Option Compare Database
Private rec As ADODB.Record
Private rst As ADODB.Recordset
Private Sub Command0_Click()
Set rec = New ADODB.Record
'This adds the DevHome Directory to the Server
rec.Open "DevHome", "URL=http://hodentek/", , _
adCreateOverwrite + adCreateCollection
rec.Close
'This adds ASPFree folder to the DevHome Folder
rec.Open "DevHome/ASPFree", "URL=http://hodentek/", , _
adCreateOverwrite + adCreateCollection
rec.Close
'similarly two other folders are added to the DevHome folder
rec.Open "DevHome/DevShed", "URL=http://hodentek/", , _
adCreateOverwrite + adCreateCollection
rec.Close
rec.Open "DevHome/DevArticles", "URL=http://hodentek/", , _
adCreateOverwrite + adCreateCollection
rec.Close
End Sub

When this code is run you may verify in your default web site that the folder DevHome is created as shown.

While the first statement creates the DevHome folder, the statements that follow create the other sub-folders as shown. Notice that the ASPFree folder is now empty. Actually the others are also empty as well.

Since these folders are created in the web site, they actually reside in the root directory of the site. This is shown in the next picture.

Creating files

Just as you created the folders, you can create files. While reviewing the earlier suggested links you might have learned the difference between a folder and a file. The folder is a container of files, whereas the file has to stand by itself. In other words the file is a non-collection type in the parlance of ADO.

Add a form in design and add the following code to the click event of the button.

Private Sub Command0_Click()
Set rec = New ADODB.Record
'Open the DevHome/AspFree folder
rec.Open "DevHome/ASPFree", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateCollection
rec.Close
'create a Test.htm file in the ASPFree folder
rec.Open "DevHome/ASPFree/Test.htm", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateNonCollection
rec.Close
'create a Test.xls file in the ASPFree folder
rec.Open "DevHome/ASPFree/Test.xls", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateNonCollection
rec.Close
'create a Test.BMP file in the ASPFree folder
rec.Open "DevHome/ASPFree/Test.bmp", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateNonCollection
rec.Close
'create a Test.aspx file in the ASPFree folder
rec.Open "DevHome/ASPFree/Test.aspx", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateNonCollection
rec.Close
'adCreateOverwrite and adCreateNonCollection etc can be added
End Sub

ADO objects are rich in enum constants; it will be useful if you can verify what they mean by going to the Object Browser. Here is one example, shown in the next picture.

Adding files to folders

In this section files will be added to the folders created. It is essential to understand the nature of files and folders as described in the links cited in the tutorial. Again the code in the following paragraph is attached to a button click event on a form. Although the creation of folders and files were shown in two separate snippets, all of this can be contained in a single snippet to create folders and files all at once. Observe the usage of enums, adCreateCollection and adCreateNonCollection in the folder/file creation process.

Option Compare Database
Private rec As ADODB.Record
Private rst As ADODB.Recordset
Private Sub Command0_Click()
Set rec = New ADODB.Record
'Open the DevHome/AspFree folder
rec.Open "DevHome/ASPFree", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateCollection
rec.Close
'create a Test.htm file in the ASPFree folder
rec.Open "DevHome/ASPFree/Test.htm", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateNonCollection
rec.Close
'create a Test.xls file in the ASPFree folder
rec.Open "DevHome/ASPFree/Test.xls", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateNonCollection
rec.Close
'create a Test.BMP file in the ASPFree folder
rec.Open "DevHome/ASPFree/Test.bmp", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateNonCollection
rec.Close
'create a Test.aspx file in the ASPFree folder
rec.Open "DevHome/ASPFree/Test.aspx", _
"URL=http://hodentek/", , _
adCreateOverwrite + adCreateNonCollection
rec.Close
'adCreateOverwrite and adCreateNonCollection etc can be added
End Sub

This creates the files in the ASPFree folder as shown in the next picture.

Since these are also created in the root directory they will be found in C:InetpubwwwrootDevHomeASPFree as shown in the next picture.

Navigating the files and folders

These files and folders can be accessed as easily as they were created. For example the statement rec.Open with the first argument set equal to: "DevHome/ASPFree" would open that folder and the various files added are its children. Hence getChildren will get all the children. The record also has its fields, one of which is ASPFree which happens to be the RESOURCE_PARSENAME. The loop puts out all the record-related information (not that of the children). These are also shown in the comments of the following code listing.

Option Compare Database
Private rec As ADODB.Record
Private chilun2 As ADODB.Record
Private Sub Command0_Click()
Set rec = New ADODB.Record
'open the ASPFREE folder
rec.Open "DevHome/ASPFree", "URL=http://hodentek/"
'count how many fields there are in the folder
MsgBox (rec.Fields.Count)
'print the name, value pair for each field
For i = 0 To rec.Fields.Count - 1
Debug.Print rec.Fields(i).Name & "  :  " _
& rec.Fields(i).Value
Next i
MsgBox ("rec fields: " & rec.Fields _
("RESOURCE_PARSENAME").Value)
MsgBox ("parentUrl" & rec.ParentURL)
'get the children of this folder
Set chilun = rec.GetChildren
'get all the children
Do Until chilun.EOF
Debug.Print chilun(0) & vbCrLf
chilun.MoveNext
Loop
Debug.Print "--------------------------------------"
End Sub

The result of running this code as it appears in the immediate window is shown in the following paragraph.

RESOURCE_PARSENAME  :  ASPFree
RESOURCE_PARENTNAME  :  http://hodentek/DevHome
RESOURCE_ABSOLUTEPARSENAME  :  http://hodentek/DevHome/ASPFree
RESOURCE_ISHIDDEN  :  
RESOURCE_ISREADONLY  :  
RESOURCE_CONTENTTYPE  :  
RESOURCE_CONTENTCLASS  :  
RESOURCE_CONTENTLANGUAGE  :  
RESOURCE_CREATIONTIME  :  
RESOURCE_LASTACCESSTIME  :  
RESOURCE_LASTWRITETIME  :  
RESOURCE_STREAMSIZE  :  
RESOURCE_ISCOLLECTION  :  True
RESOURCE_ISSTRUCTUREDDOCUMENT  :  
DEFAULT_DOCUMENT  : http://hodentek/Default.htm; 

http://hodentek/Default.asp;
http://hodentek/index.htm; http://hodentek/iisstart.asp;
RESOURCE_DISPLAYNAME  :  ASPFree
RESOURCE_ISROOT  :  False
RESOURCE_ISMARKEDFOROFFLINE  :  
RESOURCE_METATAGS  :  
RESOURCE_LISTBASETYPE  :  
DAV:name  :  ASPFree
DAV:parentname  :  http://hodentek/DevHome
DAV:displayname  :  ASPFree
DAV:iscollection  :  True
DAV:isroot  :  False
DAV:defaultdocument  : http://hodentek/Default.htm; 

http://hodentek/Default.asp;
http://hodentek/index.htm; http://hodentek/iisstart.asp; Test.aspx
Test.bmp
Test.htm
Test.xls --------------------------------------

Copying files from one folder to another

Although only copying is shown in the next code listing, it is also possible to move and delete (at your own risk) files and folders as well. Copying requires defining source and destination as well as what needs copying, as shown in the pop-up help.

Option Compare Database
Private rec As ADODB.Record
Private rec2 As ADODB.Record
Private Sub Command0_Click()
Set rec = New ADODB.Record
'open the file location
rec.Open "", "URL=http://hodentek/DevHome/ASPFree/"
MsgBox ("Rec is open" & rec.State)
'copy from ASPFree folder to DevShed folder with the overwrite flag.
rec.CopyRecord "http://hodentek/DevHome/AspFree", _
"http://hodentek/DevHome/DevShed", , , adCopyOverWrite
rec.Close
End Sub

The result of this copying is shown in the next picture.

Summary

ADO is a powerful tool and should be in your arsenal if you are working with the VB 6 environment. The Record object confers considerable power to ADO. Great caution must be exercised while moving or deleting folders in the web site as there may be other dependencies to be considered such as links. Pay careful attention to the Options argument in the syntax for all objects. When an XLS file is created, as shown in the tutorial, you could open the file with the Excel application. Also for the XLS file-type, overwriting may result in an error as the Excel program by default may not allow overwriting, and care should be exercised.