Recordsets, Records, Streams, and the Web


Recordsets, Records , Streams , and the Web

The ADO Record and Stream objects are particularly well suited for working with nonrelational data, such as files in directories and content in electronic mail systems. In addition, the OLE DB Provider for Internet Publishing extends these capabilities for accessing and manipulating files and Web folders at a Microsoft FrontPage Web site. These innovations permit Access 2003 database developers to access and manipulate these nontraditional data sources with only minor extensions to the way they work with relational databases. This section examines how to use records, streams, and recordsets to navigate through the files in Web folders at URLs.

Note  

A Uniform Resource Locator (URL) designates the location of a resource, such as a text file or a binary file on a local or networked computer. Although the concept of URLs emerged from use of the Internet, it has evolved into a broader method for specifying the location of resources on networked computers.

A Record object can point directly at an absolute URL, a combination of a relative and an absolute URL, or the current row in a recordset. (I'll define absolute and relative URLs in a moment.) When a record points at the current row of a recordset, that row is a node in a hierarchical system, which can be the files and folders of a URL.

URLs can have as many as four parts : scheme, server, path , and resource. The scheme specifies a means of reaching a resource. When working on the Internet or an intranet, you will commonly use the http: scheme. The http designation stands for Hypertext Transfer Protocol. The server references the name of the computer hosting the resource; this will often be a Web server. The path is the sequence of directories on the server that leads to the target resource. You can optionally include the resource within the path or specify it separately as the resource component of a URL.

As I hinted at earlier, there are two kinds of URLs: absolute and relative. An absolute URL designates the full location for a resource, from the scheme to the target resource. A relative URL typically will not contain the URL scheme or server, and it divides the URL into two parts, the second of which can vary. The first part of a relative URL is its base part. If all the files your application uses as resources reside in a single folder on a server that you access via the http protocol, your base URL might appear as /pathtomyfolder/ . This is considered a partial absolute URL. The filenames in the target resource can serve as the second, variable portion of the URL. You can isolate more than the resource targets in the variable portion of a URL. For example, if your application draws information from several folders in a Web server's root, you can include these folders in the variable portion of a URL. A system using relative URLs can locate resources by concatenating the base and variable portions of a URL with a standard scheme and server.

You can use URLs as inputs for the Source and ActiveConnection arguments for records and recordsets. For example, a recordset opened for a relative URL can designate the base portion of the URL for ActiveConnection and the variable portion for Source . When using absolute or relative URLs to specify what a recordset connects to, use adCmdTableDirect as the Options argument. Your statement using the recordset Open method can appear as follows :

 rst1.Open<variableURL>,<baseURL>,,,adCmdTableDirect 

The Record object also has an Open method, but this object features a tailored set of arguments that targets file access on the Web. In addition to the Source and ActiveConnection arguments, other specialized arguments facilitate the use of the Record object's Open method with Web files and folders. For example, the Record object's Open method includes arguments for creating Web folders when none exist for a URL specified by the Source and ActiveConnection arguments. Alternatively, you can force a failure if a URL points to a path or resource that does not exist. In addition, other Open method arguments let you designate a username and password for gaining access to a secure URL. Furthermore, the Record object has a ParentURL property that can expose the next highest node in a file hierarchy and a GetChildren property that returns a recordset representing the files and subdirectories within the URL for the current record.

A stream is an ADO object that resides in memory. It can store the contents of a saved file read from a disk. You can use a stream to access and manipulate the contents of these saved files with ADO programming. Streams are very flexible. For example, they enable you to work with both text and binary files. You can open a stream on objects such as a record or a binary large object (BLOB) field in a recordset. Stream objects offer different methods for reading the contents of binary and text files. Invoke the Read method to get at a specified number of bytes from a binary Stream object or the ReadText method to extract characters from a text Stream object. You can read text files in any number of characters you specify, one line at a time, or a whole file at a time. You can also persist streams to files and load them from files. You use Uniform Naming Convention (UNC) format to specify file locations.

Printing Web Folder Contents

A relatively easy way to begin understanding how to process the contents of a URL with records and recordsets is to print the contents of a Web folder. The PrintFolderContents procedure shown here demonstrates a straightforward approach to printing the contents of two Web folders. The sample demonstrates how to use the Record object to point at a node in the directory for a Web site. It starts by referencing the root directory for the CabSony1 Web server. After printing the contents of that folder, the code reorients its attention to the PMA11 Web site on the CabSony1 server. Then it applies the same technique for printing the contents of the Web site's folder.

 SubPrintFolderContents() Dimrec1AsADODB.Record Dimrst1AsADODB.Recordset     'Instantiaterecordandrecordsetobjects Setrec1=NewRecord Setrst1=NewRecordset     'SpecifytheURLoverwhichtherecordapplies 'Opentherecordonlyifitexists rec1.Open"","URL=http://CabSony1/",adModeRead,_ adOpenIfExistsOradCreateCollection 'Returnthefilesandsubdirectories 'inrec1torst1 Setrst1=rec1.GetChildren     'Printthefirstfield'svaluesintherecordset;this 'valuematchesitsrepresentationatanodeinthefile 'directorytree 'Theseresultsarefortheserver'srootdirectory Debug.Print"Serverrootdirectorycontents:" DoUntilrst1.EOF Debug.Printrst1(0).Value rst1.MoveNext Loop     'Closerecordsetandrecordobjectsforreuse 'withthedirectoryforthePMA11site rst1.Close rec1.Close rec1.Open"","URL=http://CabSony1/PMA11",adModeRead,_ adOpenIfExistsOradCreateCollection Setrst1=rec1.GetChildren     'Printthefirstfield'svaluesintherecordset;this 'valuematchesitsrepresentationatanodeinthefile 'directorytree 'TheseresultsareforthePMA11site'srootdirectory Debug.PrintvbCrLf&String(10,"_")&vbCrLf Debug.Print"PMA11siterootdirectorycontents:" DoUntilrst1.EOF Debug.Printrst1(0).Value rst1.MoveNext Loop     'Cleanupobjects rst1.Close rec1.Close Setrst1=Nothing Setrec1=Nothing     EndSub 

The procedure starts by declaring and instantiating both a record and a recordset. These two objects work together to allow the sample to enumerate the contents of a Web folder. After instantiating the rec1 object, the procedure invokes the Open method for the Recordset object. This sample references the first four arguments for the Open method. (Refer to Access 2003 Help for the complete list of Open method arguments; for example, click anywhere in the Open keyword and press F1.) The leading Source argument is an empty string because the second argument, ActiveConnection , fully specifies the Web address for the CabSony1 server. This server resides on the local intranet in my office. (You cannot access it from the Internet.) To run this sample in your office, replace the server name CabSony1 with the name of another Internet Information Services (IIS) 5.x server that is locally available. The third argument, Mode , grants read-only permission for the directory node. The fourth argument, CreateOptions , uses two intrinsic constants to indicate its objective. When used together, the adOpenIfExists constant and the adCreateCollection constant open the record to an existing directory node.

After the code opens the record to the directory node, the record will contain information about the node. The next sample you will see explores this information further. At this point, the current sample does not directly expose the files and folders of the current node. Invoke the GetChildren method against the rec1 object to return a recordset with a row for each file and folder at the current node, which is the root directory of the cab2000 Web server in the sample.

The recordset created by the GetChildren method contains many fields of information about the individual files and folders at a directory node. You can discover the names and values of these fields as you would for any recordset. For example, the PrintFolderContents sample uses a Do loop to print the first field value in the current row for the recordset returned by the GetChildren method. Each field's value contains the name of one file or folder at the directory node to which the recordset points.

After printing the names for files and folders at the server's root directory, the sample closes the rec1 and rst1 objects to prepare for their reuse with another directory. Then the code opens the rec1 object to the PMA11 directory at the Web server. This is the folder for a FrontPage Web site with the same name (PMA11). The sample then repeats the code for printing the names of the files and folders at a Web site. Figure 2-1 shows the sample output from the procedure. The root directory listing contains files, such as iisstart.asp and localstart.asp, that are typical for any IIS root folder. In addition, the root directory listing includes a folder named PMA11. The PMA11 directory listing contains folders, namely, _private and images, and a file, Default.htm. FrontPage 2003 routinely creates these folders and files for new Web sites.


Figure 2.1: Representative output from running the PrintFolderContents procedure.

Printing the Properties for Web Folder Contents

Opening either a record or a recordset with a URL designated for the Source or ActiveConnection argument creates a set of resources that provide metadata about the URL contents. For example, a recordset provides 20 fields of metadata about its URL resource. A record offers 30 fields of metadata about its URL. These fields act as properties for the URL contents. You can use these fields to gather valuable information programmatically about the files and folders within a URL. To enumerate the field names for records and recordsets based on URLs, you can adapt the code from the section "The Field Object" at the end of Chapter 1. Use the field properties discussed in that section to discover more information (such as data type and actual size ) about the recordset and record resources available for URL file and folder contents.

The OpenRecordWithRecordsetSource procedure that follows illustrates how to use selected fields for recordsets and records based on URLs. The procedure has three parts. First, it begins by opening a recordset based on the root directory for the PMA11 Web site. As mentioned previously, this Web site is on my office intranet. In this part of the code, the procedure also prints selected information about the first row in the recordset. Second, the procedure opens a record based on the recordset's first row. Again, the procedure prints selected information about the row, but this time it prints from the record based on the recordset's current row. Third, the procedure moves the current row in the recordset to the second row. Then it opens a record based on this new row, and it prints some information about the row again. After the object and variable declarations, the OpenRecordWithRecordsetSource procedure instantiates both recordset and record object references (namely, rst1 and rec1 ). The next two lines confirm the syntax for opening a recordset based on a URL. The URL points to a FrontPage 2003 Web site that contains the initial setup files and folders after its creation. The recordset contains a single row for each file and folder in the root directory of the PMA11 Web site.

The next block of code completes the first part of the procedure. This block prints selected data about the first row in the recordset using the rst1 object reference. The first set of output lines, shown in Figure 2-2, presents the output from the procedure's first part. The recordset opens with the default forward-only cursor. While the code shows how to decipher a cursor type, be aware that you cannot override the default setting when working with a URL as an ActiveConnection . (I left in the deciphering code because I thought some readers might enjoy examining the approach.) Notice in Figure 2-2 that the recordset for the URL contains 20 fields. The screen shot shows the name of the first field as well as its value, which happens to be the file or folder name in the Web site's root folder.

 SubOpenRecordWithRecordsetSource() Dimrec1AsADODB.Record Dimrst1AsADODB.Recordset DimstrURLAsString Dimenm1AsInteger Dimstrenm1AsString     'Instantiaterecordsetandrecord Setrst1=NewADODB.Recordset Setrec1=NewADODB.Record     'Openrecordsetbasedonfoldersandfilesin 'thePMA11WebsiteontheCabSony1Webserver strURL="URL=http://CabSony1/PMA11" rst1.Open"",strURL,,,adCmdTableDirect     'PrintCursorTypeEnumfortherecordset,numberof 'fieldsinrecordset,alongwithnameandvalueoffirstfield 'ontherecordset'sfirstrow enm1=rst1.CursorType strenm1=Choose(enm1+1,"adOpenForwardOnly",_ "adOpenKeyset","adOpenDynamic","adOpenStatic") Debug.Print"Selecteddataforrecordsetanditsfirstrow" Debug.Print"CursorTypeEnumnameis:"&strenm1 Debug.Print"Numberoffieldsinrecordsetis:"&_ rst1.Fields.Count Debug.Print"Nameforfirstfieldis:"&_ rst1.Fields(0).Name Debug.Print"Valueforfirstfieldis:"&_ rst1(0).Value&vbCrLf     'Openrecordbasedonrecordset'scurrentrow,and 'printthenumberoffieldsinrecord,alongwiththe 'nameandvalueofthefirstfieldintherecord rec1.Openrst1 Debug.Print"Selecteddataforrecordbasedon"&_ "recordset'sfirstrow" Debug.Print"Numberoffieldsinrecordsis:"&_ rec1.Fields.Count Debug.Print"Nameforfirstfieldis:"&_ rec1.Fields(0).Name Debug.Print"Valueforfirstfieldis:"&_ rec1(0) Debug.Printrec1(0)&_ IIf(rec1("RESOURCE_ISCOLLECTION"),_ "isafolder.","isnotafolder.")     'Movetotherecordset'snextrow;thenclosetherecord 'basedonthefirstrowandreopenitforthesecondrow rst1. 


Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net