Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2001-06-20
Medium Priority
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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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 200 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

610 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