Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Sort Recordset?

I have an ADODB.Recordset with some 50 records in, one of which includes name I would like to sort these into Alphabetical order before printing in word.  How do I do it?
0
amp81
Asked:
amp81
1 Solution
 
TimCotteeCommented:
Hi amp81,

It is always more efficient to sort the recordset when you generate it, using an Order by clause in your sql statement for example.

If you cannot do this then you can use the Recordset's .Sort method as shown below:

Sub SortRecordset(strDBPath As String, _
                  strTable As String, _
                  strSort As String, _
                  strDisplayField As String)
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   
   ' Open the connection.
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With
   
   Set rst = New ADODB.Recordset
   With rst
      ' Specify client-side cursor.
      .CursorLocation = adUseClient

      ' Open the table by using a scrolling Recordset object.
      .Open Source:= strTable, _
            ActiveConnection:= cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic

      ' Sort the Recordset object.
      .Sort = strSort

      ' Print the records.
      Do While Not .EOF
         Debug.Print .Fields(strDisplayField).Value
         .MoveNext
      Loop

      ' Close the Recordset object.
      .Close
End With

   ' Close connection and destroy object variables.
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

For example, to use this procedure to sort a Recordset object opened on the Customers table in the Northwind database by the Country and Region fields and display the values in the CustomerID field, you can use a line of code like this:

SortRecordset "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "Customers","Country, Region", "CustomerID"

The important part of this is to specify adUseClient as sorting will not work if you are using a server side cursor with access. With SQL server or another provider it may be different.

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 
amp81Author Commented:
Thanks for help working fine now.
0
 
archekryptCommented:
DON'T USE ADODC!!!!!!

Use ADO in CODE and open dbs as SQL statements, MUCH faster and MUCH more reliable...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now