ADO Returns Only Last Record
 

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?
--
Ken Hudson

ADO Returns Only Last Record
 

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?

ADO Returns Only Last Record
 

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


"keepITcool" wrote:


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?

ADO Returns Only Last Record
 

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 :

The ColNameHeader=False solved the problem.
Thanks a lot.