Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ADO recordsets

Status
Not open for further replies.

DERF81

Technical User
Oct 15, 2005
1
GB
Hi,
I am trying to create an ADO recordset with a pipe delimited text file, with no header row. I'm using a Schema.ini file to handle the delimiter.

[123.txt]
ColNameHeader=False
Format=Delimited(|)

I'm using the code below to load the recordset.

Dim D0275Conn As New ADODB.Connection
Dim D0275_Data As New ADODB.Recordset

D0275Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Proc_Dir & ";Extended Properties=""text;HDR=NO;FMT=Delimited"""

D0275_Data.Open "Select * From 123.txt", D0275Conn

This is a sample of the text file:

ZHV|DC01192845|D0275001|C|DASL|R|MANW|20050802115000|||||
25B|1507188319997|AI|PGEN|
26B|20050801|
27B|A|16.4|
27B|A|15.6|
27B|A|17.0|
27B|A|16.6|
27B|A|15.8|
27B|A|17.3|

The problem I'm having is that when I try to reference D0275.Field(2).Value it tells me that the field is null.
Which I can't understand as the only row where Field(2) is Null the thrid row, But it occurs for every row.

Any ideas where I'm going wrong?
 
If you import the data into Access, accepting all defaults, you will see that a primary key is prepended to the data and the second column is interpreted as a long integer data type.

Access therefore treats all second column values as null where the value is invalid as a long. i.e. The third row, second column is valid but all other rows (second column) are null.

You need to specify the column data types in your Schema.Ini to correctly interpret the data.
e.g.
Col1=Field1 Text Width 30
Col2=Field2 Text Width 30
etc.

Trevor
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top