I have had a problem with Access not preserving the file order of tables. It seems to take the first 5 to 10 records of the table and move them down a couple of records. I would occasionally run into this problem with Access 2000, but with Access 2007 the problem has become common. Has anyone had this same problem?

I'm not sure exactly what you are dealing with, but, I have had similar issues with tables that I am simply using for "lookups". I have solved by adding an "ID" field that is auto-number and then made that field the key index. It will keep them in order.

What do you mean by "file order of tables"? Generally databases use indices for record sorting, and the actual display or return of record order is random. In SQL, for example, it is anybody's guess as to what order the records come back in.

If there is a particular order you want records returned in, then the other post is on the right track. Create an index on the field(s) that you will most often do your lookups against. In Access you have a choice of Indexed = Yes, with or without duplicates allowed. This is somewhat equivalent to Clustered and non-clustered indices in SQL server (think book index versus table of contents).

Anywho, the "order" records are returned can be controlled based on your query language, etc.

Did any of that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for the feedback. I guess I did state my question a bit vaguely.

The jobs that I use Access to manage require that the records stay in a sequential order within a given table. Sometime that order is set by another program and the resulting table in imported back into Access. Other times a table is created using a make table query in Access. The sort order is determined by a number value in a field called SEQ_NO.

In both cases, Access will occasionally take a block of 10-20 records at the beginning of a file and move them toward the middle. Instead of the record order being 1,2,3,4... it will be 19,20,21,1,2,3,4,22,23... If the query is used to setup the file, I can run it again (not changing anything) and it will sort it correctly.

Access 2000 would do this only about 1 job in 100. Access 2007 seems to do it 1 job in 10. There is nothing in the Autoindex on Import/Create setting, so what could be doing it?

Well, unfortunately you may have to code the entire set of transactions rather than rely on Access to keep things in the order you want. What is the code for the routines the you currently run?



mmcdonal

Look it up at: http://wrox.books24x7.com