If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Is the table being opened by someone, re-sorted by a different column, and then the table is saved? If you open the table, re-sort by a different column, and then save it, it saves that sorting method.

Sorting of Tables in Access

Access maintains various indexes on tables. Some are created automatically to facilitate queries, etc. Others can be added by the user. I am not sure of how Access evaluates the standing of these indexes, but if you open the table by double-clicking on it you will see it sorted in one order or another. If you use the A-Z sorting buttons to rearrange the data, this sort order can be changed and can be stored. When you open the table again, the same sort order will apply. In the absence of all else, I think autonumber fields become the de facto sort order.
If you really need the records to be displayed in order of entry, you can try adding an autonumber field, if you have not got one already. These fields can be added restrospectively.

agree whole heartedly. with the previous contributors, a SQL table has no inherent order.. its just a meaningless collection of data. if you need the records in a specific order then you need to specify an order as part of the sql that retrieved the data in the first place.

if you dont then you leave the choice for the order to the sql engine.. it may come back indexed, it may come back according to he primary key, it may come back according to the the last used index (which 'just' happened to be in memory from the last access of that data)... y'never ever know.

so if its important be explicit.

when appending new records to an exisiting recordset its entirely possible that the new records will not be in sort order. say if you did specify a sort order, then add new records to the record set, its highly likely that those new records would be added at the end of the existing recordset, in the order they were added to the recordset, irrespective of any order you specify.

moral of the tale.. if you are then wanting to go through and find say if the record already exists, then you are better off going back to the original data and runnign a query for that exact record. bear in mind if you had two people doing the same process then the data they have in their recordset reflects the data at the time ran that query, plus any changes or alterations they may have made to the recordset.. it will; not inlcude any changes or alterations done by the other user if there are any done after the first user 'grabbed' their records. if you need the freshest data.. then requery.

It is a system table! It just seems when I use AddNew it doesn't always add the record to the bottom of the table....

Eeek - do you mean a Microsoft System table or one of your own system tables?

A set of data in relational theory is inherently unordered. As such typically in an RDBMS the physical order of data in a database is meaningless and not related to any logical order. Now - I have had a suspicion for some time that since JET is file based the order of data is significant and the order the data is displayed is always the same as the order in which it is stored (assuming there is no order by clause of course) which in turn is based on the primary key (assuming the table has one).

I know the set theory "but order has no meaning!" mantra - just curious if anyone knows how JET operates in this regard.

A Jet primary key index is a kind of clustered index. Clustered means that the physical order of the data/table matches the index, ensuring that related records are stored physically close to each other (cluster). I think that in older systems, such as this was achieved with "padding" the table with space, so that new records could be added "in the correct order".

Jet doesn't add this extra space, so rows are added in time sequence, I think, and the physical reordering (Primary Key sort order) is done when you do a Compact & Repair. For it to have impact on speed of data retrieval, one would probably also need to defragment the disk often

Some will use this to achieve the exact opposite - build relationship on unique non null indices, and use the primary key designation to "spread related records" to avoid locking conflicts.

However, in the Access UI, I'm led to believe that when opening a table, what actually happens, is a "SELECT * FROM theTable" to populate the datasheet (first populating enough records to display one screen, then continue the retrieval until all records are fetched, at which point the # of records thingie is populated), where the ORDER BY clause is probably initially the primary key, but with the possibility of saving your own sort order (hit the sort button, save on close). This saved sort order, is probably a Access GUI thing, as it is not the order you get when you retrieve the table programmatically.

So - I think you have one sort order for Jet tables (just after a Compact & Repair, it will be Primary Key sort order, but it will most probably be a mix of Primary Key sort order and time sequence order based on how long ago the last Compact & Repair was conducted), and one sort order for the Access interface (saved after changing sort order of the table in the Access interface).