i cant see a table's contents as it is

[adaniele]

sorry for the title. i didnt know what to write.
this is the situation:

i have a form where it populates a table in access (ssmdata) from an oracle table w_ssm_data). this works perfectly.

Then the form read the hole new table in access (ssmdata) to update a specific field.

Problem:
If i open the access table (ssmdata) it is sorted by the flight fields. However, when the form reads the table in order to update the specific field the table seems to not to be sorted by the flight field.

More info:
The ssmdata table in access has many primary keys (flight, departure,arrival,caterer,frequency and aircraft).

If i take a look to the W_ssm_data table in oracle it is not sorted by flight and it is sorted in the same way the form is reading the ssmdata table in access.

example:
table in access:
flight dep arr caterer aircraft frequency
1 a b a 111 67
1 a b a 222 134
2 c s c 111 5


table in oracle:
flight dep arr caterer aircraft frequency
2 c s c 111 5
1 a b a 111 67
65 t h t 252 1


when the update step starts it reads the access table as it looks in oracle. but if i open the table in access it looks sorted by flight.

thx 4 your hlp again, max

 

[adaniele]

solved

here is the solution:
i had changed the flight primary key position. it was the last one and now is the first field in the table.
So , after the change i had done nothing else.
Now, i compacted the database and it worsk perfectly.

thx again.
max

 

[neileg]

As an alternative, you could base your form on a query and apply the sort order in the query.

 

[The_Doc_Man]

adaniele,

What is happening is this: If a table has a primary key, it is stored AND RETRIEVED in the DB in PK order. If a table has an index but no PK, its order of storage and retrieval is not predictable. A COMPACT operation does, indeed, re-order tables with PKs.

The reason is that, absent a PK, a table has no inherent order. It is governed by set theory, which doesn't "require" order to be imposed on any given set.

It is the QUERY mechanism that imposes - or doesn't impose - order on a set of retrieved data. When you open a table, there is an implied query that contains all fields and honors the table's PK if it has one. If there is no PK, then the SELECT query does just that - it selects records that match the selection criteria. However, it performs that selection in the order in which it encounters the matching records. And there is where the order becomes less predictable. In a table without a PK and for which compaction has not yet occurred, record order is probably chronological with respect to when the table was updated to include the given record. And that order is very much not predictable for the average database.