Microsoft KB Archive/299484

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

Article ID: 299484

Article Last Modified on 12/3/2003

-

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

-



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.



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.



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 = &quot;Microsoft.Jet.OLEDB.4.0;&quot; .Open &quot;Data Source=D:\Nwind.mdb;&quot; End With Set cat = New ADOX.Catalog cat.ActiveConnection = cnn Set tbl = cat.Tables(&quot;Products&quot;) Debug.Print &quot;Results using the OLEDB Provider and ADOX&quot; 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 = &quot;MSDASQL.1;&quot; .Open &quot;Driver={Microsoft Access Driver (*.mdb)};&quot; & _ &quot;DBQ=D:\Nwind.mdb;&quot; End With Set cat = New ADOX.Catalog cat.ActiveConnection = cnn Set tbl = cat.Tables(&quot;Products&quot;) Debug.Print &quot;Results using the ODBC Driver and ADOX&quot; 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 = &quot;Microsoft.Jet.OLEDB.4.0;&quot; .Open &quot;Data Source=D:\Nwind.mdb;&quot; End With

Set rsSchema = cnn.OpenSchema(adSchemaColumns, _       Array(Empty, Empty, &quot;Products&quot;)) rsSchema.Sort = &quot;ORDINAL_POSITION&quot; Debug.Print &quot;Results using the OpenSchema method&quot; 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.</li> Click Command2. Note that the field names appear in their ordinal position when you use ODBC Driver.</li> Click Command3. Note that the field names appear in their ordinal position when you use OLE DB Provider with the OpenSchema method.</li></ol>

<div class="references_section">