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

MORE INFORMATION

Steps to Reproduce Behavior

1. 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
   
   
2. Save the spreadsheet and create a DSN pointing to it.
3. Start Visual Basic 6.0 from Visual Studio 6.0 Service Pack 3 or later.
4. Create a new Standard EXE and set a reference to Microsoft Active Data Objects. 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
   					

5. Make the necessary changes to the ConnectionString.
6. Run the form.
7. The first time the form runs, the error appears; it does not appear in subsequent runs.