0

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 :-)

peter_the_oak
  • 3,529
  • 3
  • 23
  • 37
  • What is the point of ordering a table ? Makes little sense since you can always order it upon opening. Note that in later versions SQL Server you can specify an order clause in saved views. Why not creating a query instead of a temp table ? – iDevlop Jun 22 '14 at 13:04
  • 1
    Whatever order you enter the records in, if you run a select query on the table there's no guarantee that it'll return the records in the same order each time.To ensure that you'll have to specify an order by clause anyway. – Swagata Jun 22 '14 at 13:11
  • @iDevlop: This is true, but the table is used immediately for an export to Excel with the `DoCmd.TransferSpreadsheet`. While this command is powerful, I've had ordering problems since ever. This is why I changed from a query as source for `TransferSpreadsheet` (most comfortable yet ordering problems) to a temporary table (still comfortable and stable). Between the table and the `TransferSpreadsheet` command are now no issues, they are in the disorder between the query and the temp table. – peter_the_oak Jun 22 '14 at 13:13
  • @Swagata: I could live with this. But I need the table created with `SELECT ... INTO temporaryTable` to be created with the right order in the first place. As mentioned in the comment before, I feed the table to `DoCmd.TransferSpreadsheet`, so it is out of my control. But I've learned, once the table is fine, the export to Excel will be fine to. – peter_the_oak Jun 22 '14 at 13:15
  • 1
    Well, not sure if this will work for you purpose but may be you can create the table first and then try a INSERT INTO....SELECT....ORDER BY query – Swagata Jun 22 '14 at 13:30
  • @Swagata: This is what I'm doing right now :-) I look forward to get the order not shuffled. I was curious if something is known about this (allegedly) MS Access bug. – peter_the_oak Jun 22 '14 at 13:35

2 Answers2

1

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I fully agree, thank you. This will lead to good coding and design. But Access forces me to ensure a certain table record order, I can't help it. Earlier, I've seen that queries don't help here. So let's fill in row after row. At least in a statement `SELECT * INTO table FROM something ORDER BY fields`, you would expect to get the order you ordered :-) – peter_the_oak Jun 22 '14 at 13:35
  • @peter_the_oak Please explain what you mean by "Access forces me to ensure a certain table record order, I can't help it". As I said, if you create a saved query named something like [QueryForExcel] then you can use `DoCmd.TransferSpreadsheet` to export that query (which has an ORDER BY clause to specify the row order). – Gord Thompson Jun 22 '14 at 13:56
  • Before I used temporary tables to feed `DoCmd.TransferSpreadsheet`, I used saved queries. Couple of months ago. Although I had a clear order in the queries, there where situations where the resulting Excel had wrong record (or row) order. After I changed to feed not queries but tables, there where no order issues in the Excels anymore. That's how Access forces me to create a table that has a certain well defined order of rows. – peter_the_oak Jun 22 '14 at 14:08
  • @peter_the_oak Interesting. I've never seen an ordered query shuffle the row order when exported to Excel, but I'll take your word for it. – Gord Thompson Jun 22 '14 at 14:14
  • Turns out that with DELETE and INSERT, even row by row, you neither have any guarantee that the order is kept. For the moment it is actually best to go back to the saved query until there are new disorder problems. – peter_the_oak Jun 23 '14 at 05:08
1

If you really need a temp table and an specific order, try using DELETE + APPEND queries instead of a MAKE TABLE query. In your "not so temp" table, you will then be able to define a Primary Key and/or a default sort order.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • This is a good working approach. As it is an export table, the customer may wish to have more fields exported. If the "not so temp" table has the new field not prepared, DELETE and APPEND won't work. Therefore I have come to drop the table and create it new each export. So it is a "not-really-but-more-than-usually-temp" table ;-) – peter_the_oak Jun 22 '14 at 14:11
  • @iDevelop: Turns out that with DELETE and INSERT, even row by row, you neither have any guarantee that the order is kept. For the moment, I have to go with the saved query way as long as I don't face problems there. – peter_the_oak Jun 23 '14 at 05:10
  • Did you create a PK in your not-so-temp table ? – iDevlop Jun 23 '14 at 13:04
  • @iDevelop: As the table was to be exported immediately to Excel, the PK column would have disturbed the result. So if I would have added it, I would had to drop it again. Now there is that certain point where your heart tells you to really stop tinkering with sth ^^ so you can still look into the mirror with good feelings ^^ ^^ Thank you very much for you effort. I've solved it by now. – peter_the_oak Jun 25 '14 at 05:41