| Sub ADOSortRecordset() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.CursorLocation = adUseClient rst.Open "Customers", cnn, adOpenKeyset, adLockOptimistic ' Sort the recordset based on Country and Region both in ascending order rst.Sort = "Country, Region" Debug.Print rst.Fields("CustomerId").Value ' Close the recordset rst.Close '------------------------------------------------------------------- 'Like the Filter property, the DAO and ADO Sort properties differ in that the DAO Sort applies to subsequently opened 'Recordset objects, and for ADO it applies to the current Recordset. 'Note that the Microsoft Jet Provider does not support the OLE DB interfaces that ADO could use to filter and sort the 'Recordset (IViewFilter and IViewSort). In the case of Filter, ADO will perform the filter itself. However, for Sort, you must 'use the Cursor Service by specifying adUseClient for the CursorLocation property prior to opening the Recordset. The 'Cursor Service will copy all of the records in the Recordset to a cache on your local machine and will build temporary 'indexes in order to perform the sorting. In many cases, you may achieve better performance by re-executing the query 'used to open the Recordset and specifying an SQL WHERE or ORDER BY clause as appropriate. 'Also, you may not get identical results with DAO and ADO when sorting Recordset objects. Different sort algorithms can 'create different sequences for records that have equal values in the sorted fields. In the example above, the DAO code 'gives 'RANCH' as the CustomerId for the first record, while the ADO code gives 'CACTU' as the CustomerId. Both results 'are valid. '------------------------------------------------------------------- End Sub |
Sort Recordset |
India web developer web development India | Freelance web development ecommerce web developer | Prayagasoft - web designer India, Ecommerce developer india, Ecommerce design