Solved

How to sort recordset opened by DAO?

Posted on 2001-06-07
18
1,010 Views
Last Modified: 2012-06-27
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!
 
0
Comment
Question by:ahpui
  • 6
  • 5
  • 4
  • +3
18 Comments
 
LVL 1

Expert Comment

by:Neal Hartman
ID: 6165699
rs.Sort = "aName"
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6165762
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
 

Author Comment

by:ahpui
ID: 6165838
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
 
LVL 7

Expert Comment

by:q2eddie
ID: 6165920
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
 

Author Comment

by:ahpui
ID: 6166016
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
 

Expert Comment

by:Hotwu
ID: 6166017
just a suggestion... maybe use the order by in your query instead of sorting on the client side?
0
 
LVL 7

Accepted Solution

by:
q2eddie earned 50 total points
ID: 6166030
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
 

Author Comment

by:ahpui
ID: 6166088
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
 
LVL 7

Expert Comment

by:q2eddie
ID: 6166100
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Expert Comment

by:Hotwu
ID: 6166108
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 6166127
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
 

Author Comment

by:ahpui
ID: 6166202
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
 
LVL 4

Expert Comment

by:wileecoy
ID: 6168040
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
 
LVL 7

Expert Comment

by:q2eddie
ID: 6168759
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
 

Author Comment

by:ahpui
ID: 6169267
no, without your suggestion I'd never solve the problem.

Thank you, everyone!
0
 

Expert Comment

by:Hotwu
ID: 6169598
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
 

Author Comment

by:ahpui
ID: 6169617
hey,

take a good look of all the posted comments, you're not the first one suggested that...
 
0
 

Expert Comment

by:Hotwu
ID: 6170011
you are right... i apologize ;).
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now