PRB: Columns Are Sorted Alphabetically When You Use ADOX to Retrieve Columns of Access Table

Article translations Article translations
Article ID: 299484 - View products that this article applies to.
This article was previously published under Q299484
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SYMPTOMS

If you use the ADOX Catalogs collection and the Microsoft OLE DB Provider for Jet to retrieve the columns of a Microsoft Access table, the columns are sorted alphabetically.

CAUSE

OLE DB Provider 4.0 for Jet retrieves columns in alphabetical order rather than in column ordinal position order.

RESOLUTION

To work around this problem, use ODBC Driver for Microsoft Access in conjunction with OLE DB Provider for ODBC. Other OLE DB Providers, such as the OLE DB Provider for SQL Server, do not pose this problem.

If you must use OLE DB Provider for Jet, use the OpenSchema method of the ADODB Connection object, and sort the resultant recordset on the ORDINAL_POSITION field.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. From the Project menu, click References, and then click Microsoft ActiveX Data Objects 2.1 and Microsoft ADO Ext. 2.1 for DDL and Security.
  3. Add three CommandButton controls to Form1.
  4. Paste the following code onto the Declarations section of Form1:
    Option Explicit
    
    Dim cnn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    
    
    Private Sub Command1_Click()
       Set cnn = New ADODB.Connection
       With cnn
          .Provider = "Microsoft.Jet.OLEDB.4.0;"
          .Open "Data Source=D:\Nwind.mdb;"
       End With
       Set cat = New ADOX.Catalog
       cat.ActiveConnection = cnn
       Set tbl = cat.Tables("Products")
       Debug.Print "Results using the OLEDB Provider and ADOX"
       For Each col In tbl.Columns
          Debug.Print col.Name
       Next
    End Sub
    
    Private Sub Command2_Click()
       Set cnn = New ADODB.Connection
       With cnn
          .Provider = "MSDASQL.1;"
          .Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "DBQ=D:\Nwind.mdb;"
       End With
       Set cat = New ADOX.Catalog
       cat.ActiveConnection = cnn
       Set tbl = cat.Tables("Products")
       Debug.Print "Results using the ODBC Driver and ADOX"
       For Each col In tbl.Columns
          Debug.Print col.Name
       Next
    End Sub
    
    
    Private Sub Command3_Click()
       Dim rsSchema As ADODB.Recordset
       Dim fld As ADODB.Field
       Dim rCriteria As Variant
    
       Set cnn = New ADODB.Connection
       With cnn
          .CursorLocation = adUseClient
          .Provider = "Microsoft.Jet.OLEDB.4.0;"
          .Open "Data Source=D:\Nwind.mdb;"
       
       End With
    
       Set rsSchema = cnn.OpenSchema(adSchemaColumns, _
            Array(Empty, Empty, "Products"))
       rsSchema.Sort = "ORDINAL_POSITION"
       Debug.Print "Results using the OpenSchema method"
       While Not rsSchema.EOF
          Debug.Print rsSchema!COLUMN_NAME
          rsSchema.MoveNext
       Wend
    End Sub
    					
  5. Modify the cnn.Open statements to point to a valid Microsoft Access database file.
  6. Run the project, and click Command1. Note that the field names are sorted alphabetically when you use OLE DB Provider.
  7. Click Command2. Note that the field names appear in their ordinal position when you use ODBC Driver.
  8. Click Command3. Note that the field names appear in their ordinal position when you use OLE DB Provider with the OpenSchema method.

REFERENCES

For more information, see the following MSDN articles:
OpenSchema Method
http://msdn.microsoft.com/en-us/library/ms676705.aspx

ADOX Table Object
http://msdn.microsoft.com/en-us/library/ms677529.aspx

Properties

Article ID: 299484 - Last Review: February 27, 2014 - Revision: 3.2
APPLIES TO
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 Service Pack 1
  • Microsoft Data Access Components 2.6
Keywords: 
kbnosurvey kbarchive kbjet kbprb KB299484

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