Can I work with a local file with ADO Record and Stream Object?

Posted on 2001-06-20
Last Modified: 2008-02-01
Can I work with a local file with ADO Record and Stream Object?

I have tried:

Dim objFileR As Record
Dim objStream As Stream

Set objFileR = New Record
Set objStream = New Stream
objFileR.Open "", "URL=D:\Temp\file.txt", _
        adModeReadWrite, adOpenIfExists Or adCreateNonCollection

objStream.Open objFileR, , adOpenStreamFromRecord

but it doesn't work.

Question by:TNL062001
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2

Expert Comment

ID: 6210112
A couple examples but both from VB/ADO .Net

May not be any use


Expert Comment

ID: 6210248
I'm not sure I fully understand your question.  Why don't you just open the file directly using the FileSystemObject?  What benefit do you get by opening it as an ADO record and then opening it as a Stream?

You can use the ADO Recordset Save method to save the recordset to a file and then open the file with the FSO.

Author Comment

ID: 6210511

I don't know, how I can answer jdcsdevx, therefore I write it here.

I used the FileSystemObject and VB statement (write, open...). But I want to learn more about ADO, when ADO allows this, I will only use ADO.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 6211072
It sounds like your trying to use ADO for the sake of it.
I would follow jdcsdevx's advice and use the best tool for the job

Accepted Solution

jdcsdevx earned 100 total points
ID: 6211516
You asked:
Can I work with a local file with ADO Record and Stream Object?

You can work with a local file with an ADO Recordset.  
You can work with a local file with the FileSystemObject.
You can't work with a local file with an ADO Recordset and the FileSystemObject at the same time like you are trying
to do

You are trying to use two dissimilar objects and they don't work together.  The way that you have written your code, Its just not going to happen.  Let me give you an example:

'this line of code won't work.  
debug.print 3*"ABC"

By definition you can't multiply numbers and strings.  It can't be done.

Now, lets look at your code.  Using your code you wrote the following:

objFileR.Open "", "URL=D:\Temp\file.txt", _
       adModeReadWrite, adOpenIfExists Or adCreateNonCollection

If you use intellisense, you can see that there are 4 cursor types and adModeReadWrite is not one of them.   You
can't mix up object models.  adModeReadWrite is a valid constant for the FileStreamObject but it can't be used with the ADODB object, by definition.  For the same reason, using intellisense, there is no lock type of the type "adOpenIfExists Or adCreateNonCollection" and so this won't work either.  You can't mix up objects.  Also I have
never seen where you can use two constants in a statement combined with "or".

Now, this is what I have been able to do, and I will include complete instructions so read on.  You CAN use ADO to connect to a datasource as long as you have a driver for that datasource.  So, if you wanted to use a text file as a datasource you could if you properly configure the driver and connect to the datasource properly.  An example of why you would want to do this is as follows:
A vendor uses a Paradox database and wants to upload files to your business.  You can't connect to the vendor's database, but the vendor can send you comma seperated text files.  When your computer is configured properly you can
connect to the vendor's csv and load the information into your database using ADO.  In this case, since you have a

comma seperated text file it makes a lot of sense to use ADO.  If you just want to read a text file however, just
use the FileSystemObject.

I assume for this section that you have a comma seperated text file that you want to use ADO to connect to.  

First you have to have a text file to connect to.  This is how I set up a small test file on my machine.  I created
a file at c:\temp\Temp.txt.  Here are the contents of the file:


Now you have to set up an ODBC DataSource on your computer using a text file driver.  This is how I did it on my NT4.0 machine.
Start->control panel->ODBC Data Sources
Click on the System DSN Tab
Click on Add
Select the Microsoft Text Driver
Click on next

You are now in the ODBC Text Setup Box
Type in a Name (JohnTest is what I used)
Description is not needed
Deselect Use Current Directory
Click on the Select Directory Button
Navigate to the c:\Temp directory
Click on OK

You are back at the ODBC Text Setup Box
Click on options
Click on Define Format
Select the file name, Temp.txt from the box on the left
Check the Column Name Header, since I am using Column name in the Comma Seperated file
Then Click on the Guess button - the defaults that it uses will probably be fine but you can edit them if needed

Click on Ok
Clik on Ok

You now have a System DSN created.

Now you can use this code to read all of the values from the text file using ADO

Dim objFileR As ADODB.Recordset
Dim strTest As String

Set objFileR = New ADODB.Recordset

objFileR.Open "Select * From Temp.txt", "JohnTest" ', adOpenKeyset, adLockReadOnly

Do Until objFileR.EOF
    strTest = strTest & objFileR.Fields("Name").Value & " " & objFileR.Fields("Age").Value & vbCrLf

Set objFileR = Nothing

MsgBox strTest

(Make sure you have a reference set up to the ADO library)

If you want to display the file using the FSO then use this code, I am using the same c:\temp\temp.txt file that I used before.

Dim objFSO As Scripting.FileSystemObject
Dim objFile As Scripting.File
Dim objFileTextStream As Scripting.TextStream
Dim strFSO As String

Set objFSO = New Scripting.FileSystemObject

objFSO.OpenTextFile "c:\temp\temp.txt", ForReading
Set objFile = objFSO.GetFile("c:\temp\temp.txt")
Set objFileTextStream = objFile.OpenAsTextStream(ForReading, TristateUseDefault)

Do Until objFileTextStream.AtEndOfStream = True
    strFSO = strFSO & objFileTextStream.ReadLine & vbCrLf

'Dereference all objects
Set objFSO = Nothing
Set objFile = Nothing
Set objFileTextStream = Nothing

MsgBox strFSO

(Make sure you have a reference set up to the Microsoft Scripting Runtime)

Good luck.

Expert Comment

ID: 6212454
I appreciate you have given a very full and valid answer, but are you aware that by proposing it as an answer you have locked out other experts contributions.

I know you are new to the site and as such would like to point out one "unwritten" rule. Experts generally offer their answers as comments leaving the questioner to decide which comment to accept. When you ask a question you get an "Accept comment as Answer" button against each comment.

Offering your answer as a comment leaves the question open for the questioner to request further clarification and other experts to make contributions.

Read the Tips on Comments and answers below and follow the link for more advice.

I hope you find EE as much fun as I do and wish you many happy hours on it in the future :-)


Expert Comment

ID: 6214691
Thank you for pointing out the unwritten rule.  I have read the tips on comments and answers as you suggested.  Thank you for your kind guidance.  Not flaming me is greatly appreciated.


Author Comment

ID: 6217308
jdcsdevx and AndrewDev,
thank you for your full answers.

the code
objFileR.Open "", "URL=D:\Temp\file.txt", _
       adModeReadWrite, adOpenIfExists Or adCreateNonCollection
objStream.Open objFileR, , adOpenStreamFromRecord

was from MSDN Help, but for Internet, that means "URL=http://.../.../file.txt". All constants stay the same.

I thought only, when ADO allows this method, then I must not add an additional reference to my project, and my project works with internet, ADO will make easier....

Thank you


Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question