Access query sequence

[Michael]

I have an MS Access 97 query which is sorted on a Reference field, defined as a text field.

Typical values in this field are:
0-003-LP
0-005-LP
0-2SDCY
0001-3.2SS
001

When the query is run in Access the records come out in the above sequence - which is correct for a text field.

However, when I run this query in VB6 (using DAO) using an Apex Combo box (3rd party control) via a Data Control, I get the records out in the following sequence:

0001-3.2SS
0-003-LP
0-005-LP
001
0-2SDCY

It is as though something (VB? Apex combo? Data Control?) is deciding that these are going to be treated as numeric fields and orders them as such.

Does anyone have any ideas what could change the sequence?

It is our current corporate policy to use VB6, DAO and Access97 so please dont suggest trying another method or version because I can't!!!



[This message has been edited by Michael (edited 01-18-2000).]

[Clunietp]

Just add an ORDER by clause at the end of your SQL statement to achieve consistency between the two

[Michael]

Too simplistic, clunietp!!! I think you may have missed the point.

I am executing the OpenRecordset method of the QueryDef object to generate the recordset, something I have done many many times before without problem, on queries much more complex than this one.

There is one thing new (for me) about this query - it contains 3 INNER JOINS and 1 RIGHT JOIN.

It is as though VB is changing the collating sequence. I have checked whatever properties and options that I can and everything appears to be OK.

Has anyone else experienced any strange changes in sequencing?

[Clunietp]

I still say use ORDER BY. It does not matter that you are joining tables, it will still work consistently.

Even if (by a small miracle) somebody here knows why the APEX grid does not sort the same as MS Access, and VB sorts different than the other two, you'll probably end up in the same boat anyway.

Just my 2 cents worth.

Anyone else? I'm sure my answer is not satisfactory to Michael.

[Michael]

I apologise to you, Clunietp, if I appeared a little harsh in my comments - I'm a good guy really!

I have solved the problem. I was using the ORDERBY - not explicitly but as a resulting of making my field the sort field which put the ORDERBY into the SQL (why am I saying this - you know that already!).

The Apex combo was a red herring - I got the same results with a standard combo, which meant that I had to look a little further into VB.

The problem was that it worked ok with Jet 3.51 in my references, but not with Jet 3.6. The puzzling thing was that all of my other queries worked fine with 3.6, whether they used the Apex combo or not!

However, because of this we have reverted to Jet 3.51 in all of our projects now.

This prompts the further question: Has anyone else experienced any "funnies" with Jet 3.6? Or, perhaps we have missed something else?

[Clunietp]

Jet 3.6 was released so that you can use DAO with Access 2000 databases, with no other major improvements. All improvements will be to ADO in the future, so move that way if you can....

If you have an Access 97 db, there is no need to upgrade to 3.6, just stick with 3.51

from MSDN:

DAO 3.6 has been updated to use the Microsoft® Jet 4.0 database engine. This includes enabling all interfaces for Unicode. Data is now provided in unicode (internationally enabled) format rather than ANSI. No other new features were implemented


HTH

Tom