Recordset order

[Steven]

I have the following code:

Dim db As Database
Dim rs As Recordset
Dim Qd As QueryDef
Set db = CurrentDb()
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

Question 01: I want to set the order of this recordset to
one of Table1's indexes. How do I do this?

Questoin 02: If there is a PrimaryKey does it always
default the PrimaryKey index order on an open recordset?

Thank you,

 

[Albert D. Kallal]

I have the following code:

Dim db As Database
Dim rs As Recordset
Dim Qd As QueryDef
Set db = CurrentDb()
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

Question 01: I want to set the order of this recordset to
one of Table1's indexes. How do I do this?

You go:

Set rs = db.OpenRecordset("select * from Table1 order by Company",

dbOpenDynaset)

So, you just simply set the order, and the index will be used (not the other
way around). By all accounts, data is un-sorted, and YOU must set the order.

Questoin 02: If there is a PrimaryKey does it always
default the PrimaryKey index order on an open recordset?

It seems to most of the time, but NOT always. You will often see this with
data in a sub-form that gets edited, and results are NOT always in the same
order.

So, if you want the order to be by primary key order, then simply base the
form, or sub-form, or recordset on a query with the order set. You can also
use the forms order by, but I find using a query much clearer.

Once again, in plain English:

if you need the order....you MUST set it

 

[John Vinson]

I have the following code:

Dim db As Database
Dim rs As Recordset
Dim Qd As QueryDef
Set db = CurrentDb()
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

Question 01: I want to set the order of this recordset to
one of Table1's indexes. How do I do this?

By opening a Query rather than a Table, and specifying a sort order in
the query:

set rs = db.OpenRecordset("SELECT * FROM table1 ORDER BY fieldname;",
dbOpenDynaset

or use the OpenRecordset method of a Querydef object.

Questoin 02: If there is a PrimaryKey does it always
default the PrimaryKey index order on an open recordset?

I would not count on it - it may, but by definition, tables are
unordered.

 

[Brendan Reynolds]

If the table is a local table, not a linked table, you can open a table type
recordset and then set it's Index property to the name of an existing index
on that table ...

Public Sub SetIndex()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblTest", dbOpenTable)
rst.Index = "TestIndex"
Debug.Print rst.Fields(0).Value
rst.Close

End Sub

This works only with table type recordsets, you'll get an error if you
attempt to set the Index of any other type of recordset, and you can only
open a table type recordset on a local table. For all other kinds of tables
and recordsets you need to use an ORDER BY clause as others have suggested.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.