Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ADO connection fm VB6 to Excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ADO connection fm VB6 to Excel
see my answer to earlier post.
keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "K.K." wrote: Hi All, I have a vb6 app that will read some data from an excel file (on |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ADO connection fm VB6 to Excel
"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. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ADO connection fm VB6 to Excel
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data connection and formatting problem | Excel Discussion (Misc queries) | |||
Problem with Ado connection fm vb6 to xls | Excel Programming | |||
MS Access Database Connection problem in Excel XP 2002 | Excel Programming | |||
Problem implementing connection point sink | Excel Programming | |||
Problem implementing connection point sink | Excel Programming |