ACC2000: Records Are Not Stored in Expected Physical Order

When you run a make-table query or programmatically copy the contents of a recordset to another recordset, the records are not always written to the destination table in the order specified by the ORDER BY clause.

The Microsoft Jet database engine does not guarantee the order in which records are physically recorded in the database. If you insert the records to the Jet database engine in a specific order, Jet may not write them in that order. This is because there are multiple caches and levels of caches between your code and the physical disk. Data does not always travel the same path to the physical disk; therefore, the data is not always written in the same order. The ORDER BY clause only controls the order in which records are read.

When you open a table or recordset that must reference the data in a specific order, you must use a query that specifies an ORDER BY clause.

This behavior is by design.

Steps to Reproduce the Behavior

1. Open the sample database Northwind.mdb.
2. Insert a new module. Paste the following code into the module.

   Function Demonstrate()
   'The following code demonstrates that the physical order of a table
   'can vary from one execution to the next. A make-table query (SELECT
   'INTO) has been chosen as the method, but the result applies equally
   'to adding records by hand, running queries manually, or copying
   'records one at a time using recordsets.
   '
   'The code operates by noting the appropriate value from the original
   'table and storing it for later use.
   'Next, the code creates a table called TestTable. The first record
   'of TestTable is compared to the record stored earlier. If they
   'match, the process is repeated.  If they do not match, the code exits
   'the loop. Depending on the version of Jet being used, there will be
   'between 1 and 10000 iterations of this loop.
   
     Dim db As DAO.Database
     Dim oldTable As DAO.Recordset
     Dim newTable As DAO.Recordset
     Dim original_value As Long
     Dim new_value As Long
     Dim count_attempts As Long
     
     Set db = CurrentDb()
     count_attempts = 0
     
     ' Fetch last order from the Order Details table.
     Set oldTable = db.OpenRecordset("SELECT OrderID FROM [Order Details]" & _
                    "ORDER BY OrderID DESC;", dbOpenSnapshot)
   
     oldTable.MoveFirst
     original_value = oldTable!OrderID
     oldTable.Close
     
     new_value = original_value
     
     Do While original_value = new_value
     'Loop until the first record in TestTable is not equal to
     'the First record that appears when you do SELECT OrderID FROM
     '[Order Details] ORDER BY OrderID DESC;
       
       'Create table using ORDER BY clause.
       db.Execute ("SELECT [Order Details].* INTO TestTable FROM " & _
                  "[Order Details] ORDER BY [Order Details].OrderID DESC;")
       DBEngine.Idle
       DoEvents
       
       'Open TestTable and read off its first record.
       Set newTable = db.OpenRecordset("TestTable", dbOpenSnapshot)
       newTable.MoveFirst
       new_value = newTable![OrderID]
       newTable.Close
       
       'Delete TestTable in preparation for next pass through the loop.
       db.Execute ("drop table TestTable;")
       
       'Increment and display counter in the Immediate window.
       count_attempts = count_attempts + 1
       Debug.Print "count_attempts = " & count_attempts
       
     Loop
     
     MsgBox "Finished"
     
   End Function
   					

3. Type the following text into the Immediate window, and then press ENTER:

   Demonstrate
   					

   The code will loop until the first record of TestTable varies from what is expected. Note that the value of count_attempts changes if you run this code several times.