In an MS Access 2010 application, I use this SQL statement:
SELECT myTable.field1, myTable.field2, ...
INTO temporaryTable
FROM myTable
ORDER BY myTable.field4, myTable.field3
The order of the records in temporaryTable often are not according to the definition in the ORDER clause, neither to the order in temporaryTable.
For some time now, I have tried ordering and copying tables There and Back Again to have the order clear and fixed, but it doesn't help. It also seems to be a phantom, sometimes it works, sometimes not. So I'll have to write a transparent but slow VBA workaround.
Does anybody know sth about this, is it a bug, and what is the best workaround? Did I miss a parameter to set?
Thanks in advance :-)
The standard response to this type of question is:
You should never depend on the rows in a table being in any particular "natural" order. This is true for most (if not all) databases, not just Access. In other words SELECT * FROM something (or equivalent) without an ORDER BY clause means that the rows can be returned in any order. In fact, such a statement may not necessarily return the rows in the same order for each invocation if you execute it more than once (although Access does tend to be fairly consistent about it).
If you need to export records to Excel in a certain order (as mentioned in comments to the question) then you should create a saved query in Access that includes an ORDER BY clause and then export the query to Excel.