different sort orders 97 v. 2000/2002

[thebolly]

i'm in the process of debugging code in vb which implements sql strings, having upgraded to access 2000/2 and have noticed that the whilst the sql queries are doing there thing correctly, the sort order of the tables is quite different although the sql is identical - it appears that 2000/2 is sorting the data whilst 97 wasn't

is this correct? why?

secondly the reason i'm debugging is because the 2000/2 upgrade is significantly slower that 97 (factor of ten) - can anyone suggest why?

many thanks as always

Ian

 

[Pat Hartman]

A2K and AXP are separate versions. Which are you upgrading to? A97 is faster than either of the two newer versions. A factor of 10 seems to be a lot though.

The order of rows in a recordset is not predictable with ANY RDBMS unless the recordset is sorted. Add an order by clause to any query where you need the rows returned in a specific order.

 

[thebolly]

to go into slightly more detail - there are two versions of my code, one - a simplified analysis, which runs equally fast under 97 and 2000 and the other, an advanced analysis which basically involves an additional string to string join in all queries which upon upgrading to 2000 runs significantly slower than before.

does access 2k have a problem with long string field joins? or does it sound like i'm barking up the wrong tree!

cheers

ian

 

[Pat Hartman]

Perhaps you need to index the fields being joined.