ADO recordsets

[DERF81]

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?

 

[TGandon]

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