Microsoft KB Archive/280049

= ACC2000: Records Are Not Stored in Expected Physical Order =

Article ID: 280049

Article Last Modified on 6/25/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q280049



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
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.



CAUSE
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.



RESOLUTION
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.



STATUS
This behavior is by design.



Steps to Reproduce the Behavior
 Open the sample database Northwind.mdb.  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(&quot;SELECT OrderID FROM [Order Details]&quot; & _                &quot;ORDER BY OrderID DESC;&quot;, 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 (&quot;SELECT [Order Details].* INTO TestTable FROM &quot; & _              &quot;[Order Details] ORDER BY [Order Details].OrderID DESC;&quot;) DBEngine.Idle DoEvents 'Open TestTable and read off its first record. Set newTable = db.OpenRecordset(&quot;TestTable&quot;, dbOpenSnapshot) newTable.MoveFirst new_value = newTable![OrderID] newTable.Close 'Delete TestTable in preparation for next pass through the loop. db.Execute (&quot;drop table TestTable;&quot;) 'Increment and display counter in the Immediate window. count_attempts = count_attempts + 1 Debug.Print &quot;count_attempts = &quot; & count_attempts Loop MsgBox &quot;Finished&quot; End Function   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. 

Additional query words: prb desired

Keywords: kbprb KB280049

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.