Solved

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

Posted on 2001-06-20
8
220 Views
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.

Thanks
TNL    
0
Comment
Question by:TNL062001
  • 3
  • 3
  • 2
8 Comments
 
LVL 5

Expert Comment

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

http://www.freevbcode.com/ShowCode.Asp?ID=2014

http://www.freevbcode.com/ShowCode.Asp?ID=2312

May not be any use

Regards
Andrew
0
 

Expert Comment

by:jdcsdevx
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.
0
 

Author Comment

by:TNL062001
ID: 6210511
Hi,

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.

Thanks
0
 
LVL 5

Expert Comment

by:AndrewDev
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
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Accepted Solution

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

Name,Age
John,29
bob,36
sarah,42

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
    objFileR.MoveNext
Loop

objFileR.Close
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
Loop
objFileTextStream.Close

'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.
   
0
 
LVL 5

Expert Comment

by:AndrewDev
ID: 6212454
jdcsdevx
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 :-)

Regards
Andrew
0
 

Expert Comment

by:jdcsdevx
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.

John
0
 

Author Comment

by:TNL062001
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

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now