Problem with ADO connection fm VB6 to Excel

[K.K.]

Hi All,

I have a vb6 app that will read some data from an excel file (on several
worksheet) then proccess them, because I don't need to update the file so I
use Ado connection to read the file (find it a little faster). However, *
sometimes * it gives me the following error when it try to execute the SQL

"Selected collating sequence not support by the operating system"

And I have the following code :


Sub ProcssInvoicingFile(pFilePath As String, pFileName As String)
Dim AdoconXls As ADODB.Connection
Dim rsXls As ADODB.Recordset


With AdoconXls
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & pFilePath & pFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
.Open
End With

sSQL = "Select * from Sheet1$ Where Len(F1) > 0"
Set rsXls = AdoconXls.Execute(sSQL) ' * * Get error here * *

'More code for processing.....


End sub

Once I hit the "Debug" button, which will bring me back to VB and if I
continue to run the app (without stopping it), it just execute as usual, no
error prompted, does anyone know what's wrong? If you need more info please
let me know, thanks in advance~!

K.K.

 

[keepITcool]

see my answer to earlier post.

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Jamie Collins]

...

I have a vb6 app that will read some data from an excel file (on several
worksheet) then proccess them, because I don't need to update the file so I
use Ado connection to read the file (find it a little faster). However, *
sometimes * it gives me the following error when it try to execute the SQL

"Selected collating sequence not support by the operating system"

With AdoconXls
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & pFilePath & pFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
.Open
End With

sSQL = "Select * from Sheet1$ Where Len(F1) > 0"

I think the problem is using

Select * from ...

This is always bad syntax for production code e.g. not explicit and
makes code harder to read, less efficient because the provider is
forced to consult a data dictionary for the column names, order of
appearance of columns is not guaranteed, addition/deletion of a column
could easily break the code, etc. But in this case there seems to be a
specific issue:

Microsoft Knowledge Base Article - 246167
PRB: Collating Sequence Error Opening ADODB Recordset the First Time
Against an Excel XLS
http://support.microsoft.com/default.aspx?scid=kb;EN-US;246167

Another candidate for problems is using IMEX=1 (Import/Export mode) in
the connection string. It's possible you need it e.g. to cast all
values as text (you may even have read this...

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

....or similar). But omit if you can. You may have problems if you use
the recordset to update the data source. To quote the help:

"You must be careful that IMEX=1 not be used
indiscriminately. This is IMPORT mode, so the
results may be unpredictable if you try to do
appends or updates of data in this mode."

Jamie.

--

 

[K.K.]

Hi cool and James, thanks for your info & suggestion, after I replace
"select * " with specified columns it works fine~ Strange enough the
"select * " works fine in other sub but not this... ?_?

K.K.