Failure in ADODB Connection

[Kou Vang]

I am trying to an ADO to retrieve data from a text file, but keep running
into a problem. Do I need to attach a certain reference in order to work?
The VBA doesn't appear to be reading certain constants in the code.
Particularly the Jet OLEDB and the Wscript at the bottom. Thanks.

Option Explicit

Dim objConnection As ADODB.Connection
Dim objRecordset As ADODB.Recordset

Const adOpenStatic = 3
Const dLockOptimistic = 3
Const adCmdText = &H1

strPathtoTextFile = "n:\mds\tfa_sect\vc data forecast\vb\"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

objRecordset.Open "SELECT * FROM TOTIS.txt", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
wscript.echo "SeqNum" & objRecordset.fields.Item("SeqNum")
wscript.echo "AADT" & objRecordset.fields.Item("AADT")
objRecordset.MoveNext
Loop

 

[Jim Vierra [661815]]

Dim objConnection As New ADODB.Connection
Dim objRecordset As New ADODB.Recordset

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoTextFile & ";" & "Extended Properties=text;HDR=YES;FMT=Delimited"
objConnection.Open strConnect

Use semi-colons to terminate strings WITHIN the connection string NOT quotes.
Try not to wrap strings in VBA as it has trouble with complicated strings and fails sometimes. It is also hard to read the line amd find errors. For very long strings do the following

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data "
strConnect = strConnect & strPathtoTextFile & ";"
strConnect = strConnect & "Extended Properties=text;HDR=YES;FMT=Delimited"


You can also use the objConnection.Provider and objConnection.Properties to build the elements of the string.
But this is harder.

You look to be using a shell script (wscript) so you are not really using VBA at all or you are mixing shell script into VBA. wscript won't work as expected in Office VBA.

This leads me to ask - What is it you are trying to accomplish? There is probably a more direct way to get what you are looking for.

 

[Kou Vang]

I am trying to read a text file with 4 to 5 columns as you can call it. But
I only want to use certain columns. With my brief attempts at smaller text
files, I found that using the FSO wasn't an option, being that u have to
reopen the file and cannot reread without doing so. Plus, I hated playing
with the arrays, and trying to get the data out of it.
I found what I thought was a solution by trying to use an ADO to read and
extract only the certain columns I want from the original text file, but of
course the code is in VB6 and not VBA, which is another story in itself.
Basically I have a huge text file, with up to a few thousand lines ontop of
several columns. An FSO just doesn't sound like the way to go it would
appear. Thanks.

 

[Jim Vierra [661815]]

Kou

Your way will work in VBA you just can't use the wscript object. Everything
else will work if you use my suggestions. I ran it in MSAccess 2003 and
Outlook 2003. Rember to set a reference to the ADO libraries either
2.5,2.6,.2.7 or 2.8 depending on the versions you have loaded. Your erro
came because you were not creating a "New" object but only create a
"pointer" to an object of the type you needed. The connection string also
had errors in it. What you are trying to do will work and it works very
well for reading "CSV" files.

 

[Kou Vang]

Thanks for the help! It finally worked. Now that I have selected the data I
want, how do I access it? Where is the data stored and what not? Or how can
I now use it?

Kou

 

[Jim Vierra [661815]]

How you use the data is up to you. It is still stored in the text file.
You can change it, move it, delete it, and add to it all you want. I
suggest going to Amazon and picking up a book on programming with VBA to
start with. Your questions indicate that you are just starting and a little
reading will go a log way to getting you whereyou need to go.

http://www.amazon.com/exec/obidos/search-handle-form/104-3671617-5879969

http://www.amazon.com/exec/obidos/t...=sr_1_23/104-3671617-5879969?v=glance&s=books