Record retrieval

M

Martin Walke

Hi all,

Can anyone tell me what dictates the order of record retrieval from a
database when it has:
a) an autonumber field which is the primary key
b) no order clause in the select statement
c) and opened using DAO under VB6?

I have a database that has these properties and is opened by simply using
the table name on its own. However, the records, when displayed, are not in
the order as defined by the autonumber field although there have been
deletions

i.e. they display similar to 1, 2, 3, 5, 10, 14, 18, 20, 48, 55, 30, 31,
34....

TIA
Martin
 
B

Bob Butler

Martin Walke said:
Hi all,

Can anyone tell me what dictates the order of record retrieval from a
database when it has:
a) an autonumber field which is the primary key
b) no order clause in the select statement
c) and opened using DAO under VB6?

I have a database that has these properties and is opened by simply
using the table name on its own. However, the records, when
displayed, are not in the order as defined by the autonumber field
although there have been deletions
Click to expand...

AFAIK there is no defined order if you don't specify one using an 'order by'
clause; the server is free to return them in whatever order is most
efficient for it.
 
M

Martin Walke

Thanks Bob. You're mirroring my own thoughts there although this is a
standard Access DB so there's no 'real' server but I guess the principle
remains the same.

No 'on order' clause means just return records in any order. It's just a bit
disconcerting for the users initially although the application doesn't care
what order the records come back in. As long as I know it won't necessarily
return in the autonumber order I can easily add the on order clause.

Thanks for the comment

Martin
 
P

Paul Clement

¤ Hi all,
¤
¤ Can anyone tell me what dictates the order of record retrieval from a
¤ database when it has:
¤ a) an autonumber field which is the primary key
¤ b) no order clause in the select statement
¤ c) and opened using DAO under VB6?
¤
¤ I have a database that has these properties and is opened by simply using
¤ the table name on its own. However, the records, when displayed, are not in
¤ the order as defined by the autonumber field although there have been
¤ deletions
¤
¤ i.e. they display similar to 1, 2, 3, 5, 10, 14, 18, 20, 48, 55, 30, 31,
¤ 34....

In Access, based upon your description, rows are returned in the order they were added to the table.


Paul ~~~ [email protected]
Microsoft MVP (Visual Basic)
 
M

MikeD

Paul Clement said:
¤ Hi all,
¤
¤ Can anyone tell me what dictates the order of record retrieval from a
¤ database when it has:
¤ a) an autonumber field which is the primary key
¤ b) no order clause in the select statement
¤ c) and opened using DAO under VB6?
¤
¤ I have a database that has these properties and is opened by simply using
¤ the table name on its own. However, the records, when displayed, are not in
¤ the order as defined by the autonumber field although there have been
¤ deletions
¤
¤ i.e. they display similar to 1, 2, 3, 5, 10, 14, 18, 20, 48, 55, 30, 31,
¤ 34....

In Access, based upon your description, rows are returned in the order
Click to expand...
they were added to the table.


Furthermore, the order of returned rows could be dependent on indexes of
other columns. Bottom line is that if you want records returned in a
certain order, use a SELECT statement and include an ORDER BY clause.
Alternatively, if you're adding these records to a ListBox, ListView, etc.,
then just have the control sort them.
Click to expand...
 
R

Rob Nicholson

In Access, based upon your description, rows are returned in the order
they were added to the table.

But while this might be true, it's a dangerous practise to rely upon it :)
Microsoft might change it later or the database may be ported to another
database.

Cheers, Rob.
 
M

Martin Walke

Thanks everyone.

I thought that the records would be returned in the order they are created
but the facts don't seem to reflect this. I'm not too worried..... as I
said, I'll just ensure that I use the 'order by' clause.

It was just an observation and having an enquiring mind........ ;-)

Martin
 
P

Paul Clement

¤ Thanks everyone.
¤
¤ I thought that the records would be returned in the order they are created
¤ but the facts don't seem to reflect this. I'm not too worried..... as I
¤ said, I'll just ensure that I use the 'order by' clause.
¤
¤ It was just an observation and having an enquiring mind........ ;-)

I can't repro the random order you mentioned. Perhaps we're using different code.

The following returns rows in the order they were added to the table (the order in which they appear
in Microsoft Access):

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rs As DAO.Recordset

Set ws = Workspaces(0)
Set db = ws.OpenDatabase("e:\My Documents\db1.mdb")

Set rs = db.OpenRecordset("AutoTable", dbOpenTable)

While Not rs.EOF
Debug.Print "ID: " & rs.Fields("ID").Value & " Field1: " & rs.Fields("Field1").Value
rs.MoveNext
Wend

rs.Close
db.Close


Paul ~~~ [email protected]
Microsoft MVP (Visual Basic)
 
Top