How to sort recordset opened by DAO?

Hi,

I opened a recordset (rs) with DAO connection like this:

    'get data source
    Set db = OpenDatabase("C:\aDatabase.mdb")
    'open access table
    Set rs = db.OpenRecordset("aTableName", dbOpenTable)

let say I have 10 fields in each record in the table "aTableName" and one of them is called "aName". How can I sort all the records in rs by the field "aName"?

Thanks in advance!
 
ahpuiAsked:
Who is Participating?
 
q2eddieConnect With a Mentor Commented:
To Hotwu:

The fact the ahpui's recordset isn't returning the correct recordcount is very strange.  Perhaps, it _is_ time to consider the order-by clause.

Hey, ahpui.

' from my comment at 06/07/2001 03:50PM PST.
For example:
Set rs = db.OpenRecordset("select * from aTableName order by aName")

#Try This
Do you get the correct recordCount now?

Set db = OpenDatabase("C:\aDatabase.mdb")
Set rs = db.OpenRecordset("select * from aTableName order by aName")

progressBar.Min = 0
progressBar.Max = rs.RecordCount 'I got 501 everytime

...

Bye. -e2
0
 
Neal HartmanCommented:
rs.Sort = "aName"
0
 
q2eddieCommented:
Hi, ahpui.

If you are refering to a table inside the mdb file, then this code may help you.  However, if you want to use the recordset instead of a tabledef, you may want to use an SQL statement with an Order-By clause.

For example:
Set rs = db.OpenRecordset("select * from aTableName order by aName")

<------------- Sample Code Begins ---------------->
Dim db As Database
Dim rs As TableDef
Dim idxName As Index

'get data source
Set db = OpenDatabase("C:\program files\devstudio\vb\biblio.mdb")
'open access table
Set rs = db.TableDefs("authors")

With rs
  ' Create new index, create and append Field
  ' objects to its Fields collection.
  Set idxNew = .CreateIndex("Authors")
  idxNew.Fields.Append .CreateField("author")
  ' Add new Index object to the Indexes collection
  ' of the Employees table collection.
  .Indexes.Append idxNew
  .Indexes.Refresh
End With
<------------- Sample Code Ends ---------------->

Bye. -e2
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ahpuiAuthor Commented:
Hi -e2, (i know you) :)

How U doing?

Hey, your suggestion works, however, with that change I can no longer get the rs.recordCount property correctly. Do you know why? Any option or type I can set to make both of them (sorting and recordCount) work? Thanks!

I need the recordCount property for progress bar.


FYI,

rs.Sort = "aName" doesn't work somehow. The program doesn't support this.

0
 
q2eddieCommented:
Hi, again.

I am doing good.

#Questions
1. How does the recordCount change?
For example:
Before index = 5 records
After Index = 8 records

I think rmcquade's suggestion is more simple.

#My Test
Before you run this code, be sure that the "authors" index has been deleted.

<--------------- Test Code Begins ------------->
On Error Resume Next
Dim db As Database
Dim rs As TableDef
Dim idxName As Index
Dim recset As Recordset

'get data source
Set db = OpenDatabase("C:\program files\devstudio\vb\biblio.mdb")
'open access table
Set rs = db.TableDefs("authors")

With rs
 Debug.Print .RecordCount
 ' Create new index, create and append Field
 ' objects to its Fields collection.
 Set idxNew = .CreateIndex("Authors")
 idxNew.Fields.Append .CreateField("author")
 ' Add new Index object to the Indexes collection
 ' of the Employees table collection.
 .Indexes.Append idxNew
 .Indexes.Refresh
 
 ' The recordset object actually uses the index.
 Set recset = db.OpenRecordset("authors")
 recset.Index = "Authors"
 Debug.Print recset.RecordCount
 recset.Close
 Set recset = Nothing

 .Indexes.Delete "Authors"
 Debug.Print .RecordCount
End With
<--------------- Test Code Ends ------------->

#Idea from rmcquade
Maybe rmcquade's idea was better.  Check this out.

<--------------- Test Code Begins ------------->
Dim db As Database
Dim rs As Recordset

'get data source
Set db = OpenDatabase("C:\aDatabase.mdb")
'open access table
Set rs = db.OpenRecordset("Select * from aTableName")

' Code from rmcquade
rs.Sort = "aName"
<--------------- Test Code Ends ------------->

Bye. -e2
0
 
ahpuiAuthor Commented:
Hi,

When I use:
Set rs = db.OpenRecordset("aTable", dbOpenTable)
rs.Sort = "aName" ---- doesn't work
but I'll get rs.recordCount correctly

When I use:
Set rs = db.OpenRecordset("SELECT * FROM aTable")
rs.Sort = "aName"  ---- works
but I'll get rs.recordCount wrong

the correct rs.recordCount, for my case, should be 8089
but whenever I try to do sorting it become 501 (everytime)
and my program error out at the point when I set progressBar.Value = 2 (2nd time in the loop)

my program run like this:

    Set db = OpenDatabase("C:\aDatabase.mdb")
    Set rs = db.OpenRecordset("SELECT * FROM aTable")
    rs.Sort = "aName"

    progressBar.Min = 0
    progressBar.Max = rs.RecordCount 'I got 501 everytime

    j = 0
    Do While Not rs.EOF
        'do something....      
   
        progressBar.Value = j 'dies at j=2 everytime
        'this doesn't work neither
        'progressBar.Value = rs.AbsolutePosition
           
        j = j + 1
        rs.MoveNext
    Loop

What's wrong??

0
 
HotwuCommented:
just a suggestion... maybe use the order by in your query instead of sorting on the client side?
0
 
ahpuiAuthor Commented:
I tried that and I still got 501. It's strange.

I even set the type to dbOpenDynaset:

Set rs = db.OpenRecordset("SELECT * FROM aTable ORDER BY aName", dbOpenDynaset)

same result again.
0
 
q2eddieCommented:
Hi, ahpui.

#Questions
1. Have you opened the table in the Visual Data Manager?
2. Do you have deleted records in there?

This is very strange.

Bye. -e2
0
 
HotwuCommented:
bro, how do you know you should be getting 8089? make sure you are connecting the right database... do me a favor... open up your access database and run this query:

SELECT count(*) FROM aTable

just to get the record count... then run this query:

SELECT * FROM aTable ORDER BY aName

to get that order by record count... if you get two different values i will retire as a software developer tomorrow.

0
 
mlmccCommented:
The problem is recordcount only works on server-side recordsets.  Some how you have changed the recordset to be client-side.  Client-side cursors read only what they need to display, in your case 501 records.  When a new record is needed another read of records from the server is done.

I am not familiar with getting data from Access databases so I don't know what the fix is but somewhere in your ADO control the cursor has been changed to client-side

good luck
mlmcc
0
 
ahpuiAuthor Commented:
guess what guys, I made it work by doing this:

Set rs = db.OpenRecordset("SELECT * FROM aTable order by aName", dbOpenDynaset)

rs.MoveLast
progressBar.Min = 0
progressBar.Max = rs.AbsolutePosition '-- this give me the correct record count  

rs.MoveFirst
Do while not rs.EOF
    'do something....
    progressBar.Value = rs.AbsolutePosition
    rs.MoveNext
Loop


Thanks for all your help!!!
But why rs.recordCount behaved like that?? Strange!?

0
 
wileecoyCommented:
Ah - the dreaded rs.movelast - rs.movefirst!  I have had the same problem.

Any time I open a recordset I immediately goto the last record and back to the first.  I have found no other way to get an accurate count of my records to begin with.

It is Strange.
0
 
q2eddieCommented:
AAAAAAAAAHHHH!!!
>Ah - the dreaded rs.movelast - rs.movefirst!  I have had
>the same problem.

D@mn it $%?!
<slaps forehead so hard he falls backward out of his chair>

Hi, ahpui.

Well, since you solved the problem yourself, you could ask Community Support to refund your question points and close this question.

Bye. -e2
0
 
ahpuiAuthor Commented:
no, without your suggestion I'd never solve the problem.

Thank you, everyone!
0
 
HotwuCommented:
that was pretty slutty... i got your asses out of the whole client side sorting in the first place... without my order by suggestion your asses would still be trying to figure this stuff out...hah
0
 
ahpuiAuthor Commented:
hey,

take a good look of all the posted comments, you're not the first one suggested that...
 
0
 
HotwuCommented:
you are right... i apologize ;).
0
All Courses

From novice to tech pro — start learning today.