View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
keepITcool keepITcool is offline
external usenet poster
 
Posts: 2,253
Default 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?