HomeDatabase Reading and Writing to Files on the Intran...
Reading and Writing to Files on the Intranet
This tutorial will show you how to read data in files on an intranet site, as well as write to files on an intranet site. You will be using the Record and Stream objects with ADO to accomplish these tasks.
Record and Stream objects introduced with ADO 2.5 give a great deal of flexibility in accessing the files and folders on the intranet site. The Record Object has been treated in some detail in earlier tutorials and the Stream object was also introduced. The ability of the Record object to create and manipulate folders and files, combined with the ability to write and read files using Stream objects, is what confers this flexibility. The Stream object gives access to the text or binary data in the buffer.
Overview of this tutorial
The tutorial provides examples of reading data in files on the intranet site, as well as writing to files on the intranet which were created using the Record object or otherwise. The data read and written to the files may be of the text or binary types. The code listings in this tutorial were all tested with MS Access 2003, used mainly for accessing the Microsoft Visual Basic Editor. The file manipulations on the web site used the intranet site http://hodentek. ADO 2.8 was used for making the reference to the library as shown in the next section.
Adding a reference to the ADO 2.8 library
The MS Access Application Stream is open in the Visual Basic editor. Go to Tools -->References... as shown in the next picture.
This brings up the References - stream window as shown. Here you use the scroll bar to scroll down and pick ADO 2.8 (msado15.dll). The default may be ADO 2.0. Now you can access all the properties and methods of all the ADO objects, including the Stream object.
In the previous article a couple of ways to open a stream object were described with examples as shown. Opening the Stream object with the URL will be used in this tutorial. However, opening the Stream object without any argument offers you the possibility of accessing elements not related to the intranet. Here are some of the ways you can open a Stream object:
Record opens with an open recordset and Stream uses the open Record.
Open a Stream using an open Record Object.
Open a Stream using an URL reference.
Open a Stream with no argument by using the Memory Stream.
Reading and writing text with the Stream Object
While the Record object deals with accessing and manipulating folders and files, the Stream object deals with what is contained in the files. The Record object can create files as described in a previous tutorial, and the Stream object can access, read from and write to these files.
The Stream object deals with two kinds of file content, text and binary. For this purpose it has several properties and methods which were not covered in the basic tutorial on the Stream object. They will be described with examples here.
Reading a text file on the intranet
The ReadText() method reads a specified number of characters from a Stream object working with a text. The related Type for a stream object is adTypeText. Stream.ReadText(NumChars) will return a string. It can therefore read a number of characters in the stream; an entire line; or the whole content in the stream. If no argument is specified, it reads the entire file.
An example of reading the text
The text to be read is on an intranet file, the content of which is shown in the next paragraph.
Test.text It must be remembered that there is nothing more difficult to plan,
more difficult of success, nor more dangerous to plan than the
creation of a new System.
The code listing shown in the next paragraph reads the text in the above file and the portion that is read is printed to the immediate screen by the debug statements.
Option Compare Database
Dim strm As ADODB.stream
Private Sub Command0_Click()
strm = New ADODB.stream
strm.Charset = "ASCII"
strm.Type = adTypeText
Debug.Print "Stream Open"
strm.Open "url=http://hodentek/DevHome/ASPFree/Test.txt", adModeReadWrite
'Reading the text here
Debug.Print "---------------reads a single line from the stream-----"
Debug.Print strm.ReadText(ADODB.StreamReadEnum.adReadLine)
Debug.Print "-------------------------------------------------------"
Debug.Print "------------------read 6 characters-------------"
Debug.Print strm.ReadText(6)
Debug.Print "----------------------------------------------"
Debug.Print "------------------read 10 characters--------------------"
Debug.Print strm.ReadText(10)
Debug.Print "-----------------------------------------------"
'Stream.EOS indicates whether or not the stream has ended
'if some content is still in left in the stream the End Of Stream(EOS) is
'false
MsgBox(strm.EOS)
Debug.Print strm.ReadText()
Debug.Print "-----------------------------------------------"
strm.Close
Debug.Print "Stream Closed"
Debug.Print "-------------------------" & vbCrLf
Set strm = Nothing
End Sub
The result of the above reading is printed to the immediate window as shown below.
Stream Open
---------------reads a single line from the stream-----
It must be remembered that there is nothing more difficult to plan,
-------------------------------------------------------
------------------read 6 characters-------------
more d
----------------------------------------------
------------------read 10 characters--------------------
ifficult of
-----------------------------------------------
success, nor more dangerous to plan than the
creation of a new System -----------------------------------------------
Stream Closed
-------------------------
When you call the ReadText() method without any argument as in the previous listing, the End of File becomes true as what is left of the stream is completely read by the last read, stream.ReadText()..
It's possible to read the text in another language, for example as shown in the next code listing which reads a quotation in French (from Napoleon) in the file French.htm. Instead of the ASCII charset, you need the 'UTF-8' charset. Since the immediate screen is strictly ASCII, the file opened was saved to a file of type *.doc.
Option Compare Database
Private strm As ADODB.stream
Private Sub Command0_Click()
Set strm = New ADODB.stream
strm.Charset = "UTF-8"
strm.Type = adTypeText
Debug.Print "Stream Open"
strm.Open "url=http://hodentek/DevHome/ASPFree/" & _
"French.htm", adModeReadWrite
strm.SaveToFile "C:/French.doc", adSaveCreateOverWrite
End Sub
This produces the output as shown written to a Word document using the Stream.SaveToFile statement. Note that some extra characters were added to the beginning of this file before <B> which were not present in the French.htm file. The reason for this is yet to be discovered.
Writing text to a file on the intranet
The content of the Test.txt file used in the above example was written using the WriteText() method of the Stream object as shown in the next listing. This example in the code listing shows both filling up an empty file on the intranet with text, as well as persisting the stream content to a file in the folder of the computer.
Option Compare Database
Private rec As ADODB.Record
Private strm As ADODB.stream
Private Sub Command0_Click()
Set rec = New ADODB.Record
rec.Open "DevHome/ASPFree/Test.txt", _
"URL=http://hodentek/", adModeReadWrite, _
adCreateOverwrite + adCreateNonCollection
Set strm = New ADODB.stream
strm.Type = adTypeText
strm.Charset = "ASCII"
strm.Open rec, adModeReadWrite, adOpenStreamFromRecord
strm.WriteText "It must be remembered that there is nothing more
difficult to plan,", adWriteLine
strm.WriteText "more difficult of success, nor more dangerous to plan
than the", adWriteLine
strm.WriteText "creation of a new System --The Prince", adWriteLine
strm.SaveToFile "outputSaveToFile.txt", adSaveCreateOverWrite
strm.Flush
strm.Close
rec.Close
End Sub
Before the code in this listing is run, the ASPFree folder does not have a Test.Txt file as shown in the next pciture.
After the record is open Test.txt, an empty file, will be created in the ASPFree folder as shown.
This file will be created with the overwrite option. The Stream object is opened with the Read Write option; it is also allowed to access the record just opened. Then a number of lines of text are written, each ending with the line separator option adWriteline. The Stream.SaveToFile statement creates the file outputSaveToFile.txt in the default directory (My Documents). Again if this file exists, it will be overwritten. The content of this file was shown in the previous section.
The listing in the next paragraph writes to the Test.htm file on the intranet site in the folder.
Set rec = New ADODB.Record
rec.Open "DevHome/ASPFree/Test.htm", _
"URL=http://hodentek/", adModeReadWrite, _
adCreateOverwrite + adCreateNonCollection
Set strm = New ADODB.stream
strm.Type = adTypeText
strm.Charset = "ASCII"
strm.Open rec, adModeReadWrite, adOpenStreamFromRecord
strm.WriteText "<html><head><title>QuoteFromPrince</title></head><body>",
adWriteLine
strm.WriteText "<center><p><font color='red'>Quote from Prince Niccolo
Machiavelli</font></p>
</center> "
strm.WriteText "<blockquote><font color='blue'>It must be remembered that
there is nothing more
difficult of success, nor more dangerous to plan than the", adWriteLine
strm.WriteText "creation of a new System --<b>The Prince</b></font>
</blockquote>", adWriteLine
strm.WriteText "</body></html>"
strm.Flush
strm.Close
rec.Close
End Sub
The Test.htm file when browsed shows the following display.
Reading and writing binary data is almost identical to reading and writing text except that the read() and write() methods have to be used. Since binary is one whole file, there are no line separators as in text. The Read() and write() methods accept byte values and return byte values.
Reading a BMP file and persisting it to another location
This example uses the stream object to read a bitmap file on one of the folders and saves it to another folder on the intranet using the SaveToFile() method. The red.bmp file represents a 16 x 16 square filled with red color and takes up 821 bytes.
Private Sub Command1_Click()
Dim strm As ADODB.stream
Set strm = New ADODB.stream
'Stream type is binary
strm.Type = adTypeBinary
'open the file red.bmp
strm.Open "Url=http://localhost/DevHome/DevArticles/red.bmp"
'save the file to root directory of ASPFree
strm.SaveToFile "C:InetpubwwwrootDevHomeASPFreeTest.bmp", _
adSaveCreateOverWrite
strm.Close
End Sub
When the Test.bmp is browsed you will see the saved bmp file as shown.
Reading a binary file and placing it in a buffer
The same red.bmp file can be read to a buffer as follows. The bytes read into the buffer are in the bufread variable. The upper bound of this shows the size of the red.bmp file read, which is 821 bytes. The read also adds an extra byte at the end position.
Private Sub Command2_Click()
Dim bufread() As Byte
Dim strm As ADODB.stream
Set strm = New ADODB.stream
strm.Type = adTypeBinary
strm.Open "URL=http://hodentek/DevHome/DevArticles/red.bmp"
bufread = strm.Read(ADODB.StreamReadEnum.adReadAll)
MsgBox (UBound(bufread))
Dim mycount As Long
For mycount = 0 To UBound(bufread)
Debug.Print mycount & "," & (bufread(mycount))
Next mycount
strm.Close
End Sub
Writing a binary file
The data that gets into the buffer will be a byte array and the Stream's write method will write to this array as shown in the listing.
Option Compare Database
Private Sub Command0_Click()
Dim mybuf(3) As Byte
Dim rec As ADODB.Record
Dim strm As ADODB.stream
Set rec = New ADODB.Record
Set strm = New ADODB.stream
rec.Open "jay.dat", "URL=http://hodentek/DevHome/DevShed/", _
adModeReadWrite, adCreateNonCollection + adCreateOverwrite
strm.Type = adTypeBinary
strm.Open rec, adModeReadWrite, adOpenStreamFromRecord
mybuf(0) = 100
mybuf(1) = 25
mybuf(2) = 210
strm.Write (mybuf)
'jay.dat file with 4 Bytes will be created
'with the 4th byte, the end of file
strm.Close
rec.Close
End Sub
Summary
The tutorial describes with examples writing and reading from files on the intranet. The process of reading and writing is much cleaner than using the earlier versions of ADO (2.0, 2.1). Since the Stream object can be opened without any arguments, the stream will be in memory and can be persisted to a file on the hard disk. This is a risky procedure as any program which gives access to writing files to the hard disk should be avoided, or used with caution. The IE browser has inherent support for ADODB.Stream objects, and as vulnerabilities in IE can be exploited, it needs to be disabled. Follow this link for a recipe to disable the support and use caution when saving files to hard disk.
Using the SaveToFile and LoadFile features (which were not discussed, but easy to implement) makes transferring binary files very easy. The argument for the ReadText() which reads everything has to be looked up in a reference or guessed, since intellisense gives no help. While reading text files, unless the charset is specified as ASCII, setting stream.type alone will not suffice.
Database 
ADO`s Stream Object
