Default sort order

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mcollier - 15 Dec 2005 22:22 GMT

I have some VB6 code that calls a SELECT query in Microsoft Access.
When I run the query from access, the results appear in not particular
order. However, when I call the query from VB6, via ADO, the rows are
returned in order by one particular column/field. There is no ORDER BY
on the query.

For example, in Access, the rows may be returned as:
RowID
------
1
5
3
8
45
23

But, when the same query is call from my VB6 app, the rows are returned
as:
RowID
-----
1
3
5
8
45
23

As far as I know, there is nothing special about the RowID (for the
example) column/field. It is the first column accessed in my VB6 code
however. There is no Sort on the ADO recordset either.

I'm at a loss as to how the results are returned in a different order
depending on how I access the query. Any help would be greatly
appreciated.

Thanks!

Reply to this Message

Tim Marshall - 15 Dec 2005 22:26 GMT

> I'm at a loss as to how the results are returned in a different order
> depending on how I access the query. Any help would be greatly
> appreciated.

Hi Michael, I can't give you any insight on this, though others may.
However, in just about any SQL, including Jet (which is the default
database engine for MS Access - so techinically speaking you are
accessing a "Jet" database, not an "Access" database from VB6), you can
NEVER be guaranteed any return sort order unless you are using an ORDER
BY clause or a GROUP BY (for aggregate values).

It would probably be good advice to make sure you tack on an ORDER BY
RowID or whatever.

Signature

Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Reply to this Message

mcollier - 16 Dec 2005 15:53 GMT

I have added the ORDER BY clause to the SELECT statement. I totally
agree that ORDER BY clause should be there.

I still don't understand how the results appear to be ordered
differently based on how I access the query (when the ORDER BY clause
was not there).

Thanks!

Reply to this Message

Paul Clement - 16 Dec 2005 14:58 GMT

¤ I have some VB6 code that calls a SELECT query in Microsoft Access.
¤ When I run the query from access, the results appear in not particular
¤ order. However, when I call the query from VB6, via ADO, the rows are
¤ returned in order by one particular column/field. There is no ORDER BY
¤ on the query.
¤
¤ For example, in Access, the rows may be returned as:
¤ RowID
¤ ------
¤ 1
¤ 5
¤ 3
¤ 8
¤ 45
¤ 23
¤
¤ But, when the same query is call from my VB6 app, the rows are returned
¤ as:
¤ RowID
¤ -----
¤ 1
¤ 3
¤ 5
¤ 8
¤ 45
¤ 23
¤
¤ As far as I know, there is nothing special about the RowID (for the
¤ example) column/field. It is the first column accessed in my VB6 code
¤ however. There is no Sort on the ADO recordset either.
¤
¤ I'm at a loss as to how the results are returned in a different order
¤ depending on how I access the query. Any help would be greatly
¤ appreciated.

What is the data type of the field by which the rows are ordered? Is it an autoincrement field? Is
it a primary key field?

Both Access and ADO should return results that are ordered in some way. By default, Access typically
will return rows in the order in which they were added.

Paul
~~~~
Microsoft MVP (Visual Basic)

Reply to this Message

mcollier - 16 Dec 2005 15:59 GMT

In Access, the results of the query appear to be in ascending order of
a DateTime field. This DateTime field is from one of the primary
tables in the query, which includes two INNER JOINs. It is not a
primary key field. It would appear to me that Access is returning the
rows in the order they were added.

How/Why are the results returned in a different order when the query is
executed from ADO?

Thanks!

Reply to this Message

Paul Clement - 16 Dec 2005 18:02 GMT

¤ In Access, the results of the query appear to be in ascending order of
¤ a DateTime field. This DateTime field is from one of the primary
¤ tables in the query, which includes two INNER JOINs. It is not a
¤ primary key field. It would appear to me that Access is returning the
¤ rows in the order they were added.
¤
¤ How/Why are the results returned in a different order when the query is
¤ executed from ADO?

It's possible ADO is returning the data set based upon an autonumber column or by primary key. That
was why I was asking about the data type of the column upon which the data set was ordered.

As Randy has mentioned, relying on the default behavior of the data access mechanism isn't
necessarily going to provide you with the order you desire. That is why he suggested using ORDER BY.

Paul
~~~~
Microsoft MVP (Visual Basic)

Reply to this Message

Randy Harris - 16 Dec 2005 16:19 GMT

> ¤ I have some VB6 code that calls a SELECT query in Microsoft Access.
> ¤ When I run the query from access, the results appear in not particular
[quoted text clipped - 20 lines]
> ~~~~
> Microsoft MVP (Visual Basic)

In my experience, you simply cannot rely on records being returned in any
particular order unless it is specified by ORDER BY in a query. Access and
ADO will often return records in the sequence that they were entered but
that can change. Deleting records or compacting a database can cause it to
change abruptly.

Signature

Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Reply to this Message