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.
"Jamie Collins" wrote in message
om...
"K.K." wrote ...

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...b;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...al_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.

--