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

Article translations Article translations
Article ID: 246167 - View products that this article applies to.
This article was previously published under Q246167
Expand all | Collapse all

On This Page

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.

CAUSE

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

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.

Properties

Article ID: 246167 - Last Review: June 24, 2004 - Revision: 3.1
APPLIES TO
  • Microsoft Visual Basic 6.0 Learning Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 3
  • Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 4
  • Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 5
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
Keywords: 
kbdatabase kbiisam kbprb KB246167

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com