VB6 Bug Fun : ADO, Excel, Text Files
Run time error '-2147467259 (80004005)' : [Microsoft][ODBC Excel Driver]Selected collating sequence not supported by the operating system
After a few minutes with my pal Google, here is the MSFT KB url:
PRB: Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS
SYMPTOMS
Opening an Microsoft Excel spreadsheet from within Visual Basic in Visual Studio 6.0 Service Pack 3 (or later) Integrated Development Environment (IDE) with the Excel/ODBC/ISAM driver generates the following run-time error: Run time error '-2147467259 (80004005)' : [Microsoft][ODBC Excel Driver]Selected collating sequence not supported by the operating system
This problem occurs every time that you run the code within the Visual Basic IDE.NOTE This error does not occur if the all the fields are selected in the SQL statement, for example:
SELECT * FROM ...Also, this problem does not occur within a compiled EXE.
A solution to this problem? None. Just remember when debugging in the IDE and you see an error, just re-run the select statement, second time through it works fine.
Next. Have you ever tried using the Jet/OleDB driver to open a Tab Delimited Text file, without using Schema.ini? From what I can tell (googling and too much testing for what it is worth), you CANT. You must write out to the same folder as the data, a Schema.ini which is properly formatted for what you would like.
Ugly, since most of us like to run as non-administrator it really sucks when we are not sure if we can even write to that same folder, so now we are forced to check for write permissions, attempt to write and if there are any failures kindly let the end user know that they must move the file to a "better" location. Why oh why!?
The second option would be to overwite the default Format option in the registry; a quote from a article on MSDN reads:
"Admittedly, opening the Registry, changing the value of Format, running the script, re-opening the Registry, and then changing the value back again can be a bit tedious, to say the least. But remember, you can change the registry from within your script."
It indicates NOTHING about security and running as non-administrator. Sounds like a support nightmare to me.
4 Comments
Comments have been disabled for this content.
Lance Gilroy said
Once you've imported the large text file using the ADO Connection in excel, you can use Data-> Split to cells which does the same thing as when you delimit a file upon open.
Anele said
Checked with microsoft about this, no solution, though this makes it hard to debug your program as you don't know the result of the error.
J.Ryder said
Can't open a Tab Delimited Text file without using Schema.ini? Use the text driver instead of Jet. No schema needed. "Driver={Microsoft Text Driver (*.txt; *.csv)};Extensions=csv;"
Kashif ali said
I am working on VB6, using ADO for SQL2000 Database and facing randumly same error in comiled EXE.