Select the product you need help with

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

Article ID: 299484 - View products that this article applies to.

This article was previously published under Q299484

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.

Back to the top | Give Feedback

CAUSE

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

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

MORE INFORMATION

Steps to Reproduce Behavior

Create a new Standard EXE project in Visual Basic. Form1 is created by default.
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.
Add three CommandButton controls to Form1.

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

Modify the cnn.Open statements to point to a valid Microsoft Access database file.
Run the project, and click Command1. Note that the field names are sorted alphabetically when you use OLE DB Provider.
Click Command2. Note that the field names appear in their ordinal position when you use ODBC Driver.
Click Command3. Note that the field names appear in their ordinal position when you use OLE DB Provider with the OpenSchema method.