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!
6  7256 
mcollier wrote: 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.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
On 15 Dec 2005 14:22:02 -0800, "mcollier" <mi*************@gmail.com> wrote:
¤ 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)
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!
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!
"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:p6********************************@4ax.com... On 15 Dec 2005 14:22:02 -0800, "mcollier" <mi*************@gmail.com>
wrote:
¤ 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.
¤
¤
¤ 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)
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.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
On 16 Dec 2005 07:59:46 -0800, "mcollier" <mi*************@gmail.com> wrote:
¤ 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)
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mike |
last post by:
Is there a way to determine what a user's default email client is? I
read a post from 3 years ago that said no. I guess I'm hoping
something has come along since then.
|
by: Mike Nolan |
last post by:
I notice that 7.4 doesn't do default ordering on a 'group by', so you have
to throw in an 'order by' clause to get the output in ascending group order.
Is this something that most RDB's have...
|
by: Steve Crawford |
last post by:
I am suffering some sort order confusion. Given a database, "foo",
with a single character(4) column of data left padded with spaces I
get:
select * from foo order by somechars;
somechars...
|
by: adrian.chandler |
last post by:
Hi all,
I have been using letter and symbol codes such as GNU< GNU\ GNU}
GNUˆ in an Access table. I was surprised to see that when the table
was sorted on this field, the order is:
GNUˆ...
|
by: Daves |
last post by:
I have a gridview displaying News items and fetches the data through a
(little complicated) sql stored procedure... The sorting seems to be done in
the gridview since there is no sorting...
|
by: weetat.yeo |
last post by:
Hi all ,
I have sql statement below ,
SELECT
serial_no,host_name,chasis_model,chasis_flash_size,chasis_dram_size,...
|
by: =?Utf-8?B?V2ViQnVpbGRlcjQ1MQ==?= |
last post by:
For performance reasons i can not set a sort order on a dataset returned from
a stored procedure. Ideally in the sp i'd set the company name as the order
and just load the gridview, however the...
|
by: artev |
last post by:
in the default sort which is the compare function used?
alphabetical ordination is so:arry.sort();
but which is the default function used?
is there a good link wich example for alphabetical...
|
by: JHite |
last post by:
I am using Access 2003 on Windows XP.
This is a simple database that contains “tblStaffers” containing names of the office staffers, “tblProjects” containing names of the office projects, and...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
