ACC2000: Records Are Not Stored in Expected Physical Order (280049)

MORE INFORMATION

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.