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
SYMPTOMSOpening 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 systemThis 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.