Prev: 2.6 to 2.8
Next: VO 2.7 - VO 2.8 DBF size. How can it be?
From: John Martens on 13 Feb 2010 04:31
I open a cetrain XLS file like this:

SELF:oHost:AccessMode := SQL_MODE_READ_ONLY
cCo := "Driver=Microsoft Excel Driver (*.xls);Dbq="+SELF:cFileNm
lResult := SELF:oHost:DriverConnect(,SQL_DRIVER_COMPLETE_REQUIRED,cCo)
IF lResult
SELF:oSqlSelect := SQLSelect{"SELECT * FROM [CALLS$]",SELF:oHost}
ENDIF

Most records in the SqlSelect are OK but there are record that are not
rightly read and then they are filled with the last record that could be
read.
There can be 20 of these and the the next one is read OK again.

Is this any problem of MS ODBC drivers or is this a problem of VO ?

John
From: John Martens on 16 Feb 2010 11:12
If I open the file from a VBA macro (see below) the problem is not
there. All rows are correctly read.

So the problem is in VO ?

John



Option Explicit
Sub Export()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim objField As ADODB.Field
Dim intFile As Integer
Dim strDataLine As String

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

intFile = FreeFile
Open ActiveWorkbook.Path & "Calls.csv" For Output As #intFile

With conn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver
(*.xls)};DBQ=" & ActiveWorkbook.Path & ActiveWorkbook.Name & ";
ReadOnly=False;"
.Open
End With

' conn.Open "Driver= {Microsoft Excel
Driver(*.xls)};DriverId=790;Dbq=" & ActiveWorkbook.Path &
ActiveWorkbook.Name

rs.Open "SELECT * FROM [Calls$]", conn

Do While Not rs.EOF
For Each objField In rs.Fields
strDataLine = strDataLine & Chr(34) & "," & Chr(34) &
objField.Value
Next objField
strDataLine = Chr(34) & strDataLine & Chr(34)
Print #intFile, strDataLine
strDataLine = ""
rs.MoveNext
Loop

Close #intFile

rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing

End Sub


Op 13-2-2010 10:31, John Martens schreef:
> I open a cetrain XLS file like this:
>
> SELF:oHost:AccessMode := SQL_MODE_READ_ONLY
> cCo := "Driver=Microsoft Excel Driver (*.xls);Dbq="+SELF:cFileNm
> lResult := SELF:oHost:DriverConnect(,SQL_DRIVER_COMPLETE_REQUIRED,cCo)
> IF lResult
> SELF:oSqlSelect := SQLSelect{"SELECT * FROM [CALLS$]",SELF:oHost}
> ENDIF
>
> Most records in the SqlSelect are OK but there are record that are not
> rightly read and then they are filled with the last record that could be
> read.
> There can be 20 of these and the the next one is read OK again.
>
> Is this any problem of MS ODBC drivers or is this a problem of VO ?
>
> John
 | 
Pages: 1
Prev: 2.6 to 2.8
Next: VO 2.7 - VO 2.8 DBF size. How can it be?