ADO Returns Only Last Record

[Ken Hudson]

Courtesy of Robin Hammond, I have the following code:

Option Explicit
Sub OpenRSFromText()
Dim oConn As ADODB.Connection
Dim rsInput As ADODB.Recordset
Dim strPath As String

strPath = "F:\DATA\XCELData\MACROS\PTFUNDS\"
Set oConn = New ADODB.Connection
Set rsInput = New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath & ";" & _
"Extended Properties=""text;HDR=NO;FMT=FixedLength"""

rsInput.Open "SELECT * FROM PtFunds.txt", _
oConn, adOpenStatic, adLockOptimistic, adCmdText
rsInput.Filter = "ID = '6403'"

ActiveSheet.Cells(1, 1).CopyFromRecordset rsInput
MsgBox rsInput.RecordCount & " records found with ID 6403"
End Sub


I have the following sample records in PtFunds.txt

6403A
6400B
6403C


I have the following Schema.ini in the same folder as the test file.

[PtFunds.txt]
Format=FixedLength

Col1=ID Text Width 4
Col2=AcctName Text Width 1

When I run the code, it returns only the last record. The first is not
included.

Any ideas why?

 

[keepITcool]

The problem may be related to ADO still interpreting the first record
as a header.

First guess capitalization of the argument:
: try HDR=No (doubtfull scenario ...


If you have JETSQL40.CHM help file on your computer
(installed with Access in Office11\1033 subfolder) check out following:
Windows Registry Settings for External Data Sources
.. Initializing the Text and HTML Data Source Driver.

There's also a section on customizing the Schema.Ini

I think you'll need following line:
[PtFunds.txt]
Format=FixedLength
ColNameHeader=False




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ken Hudson wrote :

 

[Ken Hudson]

The ColNameHeader=False solved the problem.
Thanks a lot.
--
Ken Hudson

The problem may be related to ADO still interpreting the first record
as a header.

First guess capitalization of the argument:
: try HDR=No (doubtfull scenario ...


If you have JETSQL40.CHM help file on your computer
(installed with Access in Office11\1033 subfolder) check out following:
Windows Registry Settings for External Data Sources
.. Initializing the Text and HTML Data Source Driver.

There's also a section on customizing the Schema.Ini

I think you'll need following line:
[PtFunds.txt]
Format=FixedLength
ColNameHeader=False




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ken Hudson wrote :

Courtesy of Robin Hammond, I have the following code:

Option Explicit
Sub OpenRSFromText()
Dim oConn As ADODB.Connection
Dim rsInput As ADODB.Recordset
Dim strPath As String

strPath = "F:\DATA\XCELData\MACROS\PTFUNDS\"
Set oConn = New ADODB.Connection
Set rsInput = New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath & ";" & _
"Extended Properties=""text;HDR=NO;FMT=FixedLength"""

rsInput.Open "SELECT * FROM PtFunds.txt", _
oConn, adOpenStatic, adLockOptimistic, adCmdText
rsInput.Filter = "ID = '6403'"

ActiveSheet.Cells(1, 1).CopyFromRecordset rsInput
MsgBox rsInput.RecordCount & " records found with ID 6403"
End Sub


I have the following sample records in PtFunds.txt

6403A
6400B
6403C


I have the following Schema.ini in the same folder as the test file.

[PtFunds.txt]
Format=FixedLength

Col1=ID Text Width 4
Col2=AcctName Text Width 1

When I run the code, it returns only the last record. The first is
not included.

Any ideas why?

 

[keepITcool]

good.

be aware that you problem analysis was wrong:
it is not:
Returns only Last Record.
it is:
Does not return First record.

<vbg>

be sure to keep a reference to that help file handy.
(I've got it in my Quicklinks). It contains a lot of
useful info/reference stuff re SQL and ADO settings

else: www.DEVGURU.COM has a good ADO reference guide online.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ken Hudson wrote :