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

This article was previously published under Q299484
This article has been archived. It is offered "as is" and will no longer be updated.
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 ExplicitDim cnn As ADODB.ConnectionDim cat As ADOX.CatalogDim tbl As ADOX.TableDim col As ADOX.ColumnPrivate 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   NextEnd SubPrivate 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   NextEnd SubPrivate 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   WendEnd 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:
incorrect sorting fields MSAccess Acc2k 3.51
Properties

Article ID: 299484 - Last Review: 02/27/2014 21:08:41 - Revision: 3.2

  • 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
  • kbnosurvey kbarchive kbjet kbprb KB299484
Feedback