Bouncing Recordnumbers

[Gnasch]

I have solved this problem, at least from the users point of view. But perhaps someone can explain the mechanics behind why this happens.

I have an access 2007 project tied to a mssql server. On my form, whenever I issue an recordset.requerry command, the recordset that is reuturned has different recordnumbers.

For instance.

If i have a simple table with one field of names, this is what it looks like prior to the requerry.

Record 1 - "Tommy"
Record 2 - "Billy"
Record 3 - "Alice"
Record 4 - "John"


If I change "Tommy" to "Tom" by updating the recordset then issue a querry I get this back.

Record 1 - "Alice"
Record 2 - "Billy"
Record 3 - "John"
Record 4 - "Tommy"

So when I set the bookmark of "1" after the requerry instead of "Tommy" i get "Alice".

I solved this problem by using an Order By on the Identity field in the main table of the querry. I am still confused as to why the recordset would bounce around like that.

 

[jdraw]

In Ms Access and other RDBMS

the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified in the SELECT statement that queries the table.