PRB: Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS

ID: Q246167

The information in this article applies to:

Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 6.0
ActiveX Data Objects (ADO), version 2.1 SP2

SYMPTOMS

Opening an Microsoft Excel spreadsheet from within Visual Basic in Visual Studio 6.0 Service Pack 3 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 only occurs the first time the code is executed within the Visual Basic IDE. It does not occur subsequent times.

CAUSE

This is a problem within the Visual Basic IDE starting with service pack 3.

RESOLUTION

This error does not occur if the all the fields are selected in the SQL statement, such as "SELECT * FROM ...".

This does not occur within a compiled EXE.

STATUS

Microsoft is researching this problem and will post new information here as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

Create a spreadsheet in Microsoft Excel 97 or Microsoft Excel 2000 and place the following values in the prescribe fields:

A1 = F1
B1 = F2
C1 = F3

Save the spreadsheet and create a DSN pointing to it.

Start Visual Basic 6.0 from Visual Studio 6.0 Service Pack 3.

Create a new Standard EXE and paste the following code into the form:


Public Sub Form_Load()

    Dim constring As String
    Dim Connection As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

'   define a DSN IMPORT-XLS, using the Excel ODBC driver, and point it to the xls file
     constring = "Provider=MSDASQL;DSN=IMPORT-XLS"
     
     Connection.Open constring
     Set cmd.ActiveConnection = Connection
     cmd.CommandType = adCmdText
     
'   The following .CommandText causes the error, but only the first time it is run within the ide.
   cmd.CommandText = "Select F1, F2 from ""Sheet1$"""
'   The following .CommandText will not cause the error.
'    cmd.CommandText = "Select * from ""Sheet1$"""

     rs.CursorLocation = adUseClient
     rs.CursorType = adOpenStatic
     rs.LockType = adLockReadOnly
     rs.Open cmd
     While Not rs.EOF
        MsgBox rs.Fields(0)
        rs.MoveNext
     Wend

End Sub

Make the necessary changes to the ConnectionString.

Run the form.

The first time the form runs, the error appears; it does not appear in subsequent runs.

Additional query words:

Keywords : kbExcel kbIISAM kbMDAC kbODBC KbVBA kbVBp kbVBp600 kbGrpVBDB kbDSupport
Version : WINDOWS:2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbprb