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

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.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

jdcsdevxConnect With a Mentor Commented:
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.
A couple examples but both from VB/ADO .Net

May not be any use

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.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

TNL062001Author Commented:

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.

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
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 :-)

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.

TNL062001Author Commented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.