MS Access Sorting

[Bob Laubla]

Hello
I have a very complex maketable query with many records and involving multiple VB functions which call other functions.
I need this table to be sorted by the first field.
But no matter what I do, there is a small chance of the table coming out unsorted.
I have tried sorting it with the 'make table' query.
I have tried sorting it with Word during output (my current configuration runs 4 of these).
I have tried reading it into an ADODB.Recordset, sorting, and outputing
I have tried running another maketable query from that table and sorting it in that query.
Nothing works, there is always a small chance of it not sorting. By not sorting I mean that the table looks like the program tried to sort it and then gave up halfway. The most common unsorted output is a sorted table cut in half, with the bottom half being on top.
What is going on?
Also, we have our database on the server and users have custom front ends. But for quicker access I copy the database to my desktop. My boss however, uses the database over the network. What we have noticed is that an unsorted table occurs more times to her than to me (we think)-- if that helps.
The field I'm sorting on is a text field in the form ###?.
We use Access 2003 SP3
Any help would be greatly appreciated.

[Stewart Ross]

...But no matter what I do, there is a small chance of the table coming out unsorted....

Hi Bob. I've also had sorting issues with Access 2003 on complex queries. In one case a query with mutliple intermediate queries sorted into order on multiple fields failed on occasions, and running this same query twice in succession on static data produced different sort orders. Initially with around 4000 rows the failures were rare, but by the time this had grown to around 7000 rows of data the failure was occurring at random but every time it was run (which was on a daily basis). The sorting was crucial to department output in my application, and caused output errors in exports to Excel.

Given your experience too I would reckon this is a bug in Access 2003 somewhere.

I resorted to outputting the mainly-sorted complex query to a temporary table, then outputting from the temporary table using a query that did another, final, sort. There was no other processing done in this query. This has performed with 100% reliability ever since.

-Stewart

[Bob Laubla]

Well, the thing is that I don't have that much records. The table usually has about 300-400 rows. It's just that per row, the last few cells have 10-60 lines of text in them.

Hmm, this gives me an idea. I will create a unique id for each row, then create 2 tables, first one with the sorting number and the id and the second one with the id and the rest of the row. Then sort the first simple one, and then link on id to the rest of the row.

Thanks for your help.