Problem with Access Records Order

jamiecrofts

New member
Local time
Today, 23:42
Joined
Mar 22, 2007
Messages
1
Hi,

I apologise if this has been posted in the wrong place.

I am currently making changes to a VB front end for a database of jobs in our company. we have approx 2000 job records in the job table. The program uses a recordset to move sequentially up or down the table, record by record as it searches.

The code is doing its job fine, but for some reason or another, the records in the database are not in order. They loosely follow the order you would expect from 1 to 2000, but every now and then deviates. Obviously, when pulling data out one record at a time, this gives the data in the wrong order.

I did not design the database. There was orignally no primary key or indexing. The new job number (an obvious choice for a primary key) is generated by the exectuable program.

I have spent hours trying to apply sorts in different ways, and reading about how records are stored, and my limited understanding has led me to believe that merely sorting records in access is not enough to change the order that they are physically stored in.

My coding abillity is not good enough code round this problem, and the ideal fix would be to find out some way of forcing an ordering from now on (ie would making job number and indexed primary key now prevent this happening to future records) and to find some way of re ordering the previous records (not just sorting them to be viewed in order).

Am I being really silly and missing something or is this a problem people have had before?

Thanks in advance,

Jamie
 
A table is an unordered dataset. Access doesn't care about order. When you sort a table, you are only sorting the display, not the underlying data You need to impose an order using a timestamp, incrementing number or something like that and then use this to order in a query. Use the query as the basis of your code.
 
Neileg gave you the correct suggestion. I'll back that with a little-appreciated fact.

ALL databases are based on set theory. In the theoretical case, when you do something to the members of the set, you do it to all of them - with no statement of ordering. That is, in theory, everything that happens to set members MIGHT AS WELL happen in parallel without regard to order because set theory doesn't include the word "ordered." It is an artifact of presentation. Which is why neileg suggested the query.

A query includes the syntax for "ORDER BY" to assert a particular order. A table, which is a SET (hint, hint) of records, has no true order. If you open the table and click on one of the fields to sort it, you sort the PRESENTATION of the table ON YOUR SCREEN. It is like you created a virtual query that is the equivalent of SELECT * FROM MYTABLE ORDER BY {whatever was last clicked in the field list}; If you save that, you DON'T SAVE THE TABLE. You save the presentation information.

Never ever presume anything to do with order when working directly with tables. That order doesn't truly exist. (Particularly since you just admitted there was no prime key.)

OK, now, here's how I let you off the hook. Neileg is correct, there is no order to a table - with one exception. If you declare a proper prime key (i.e. unique) and immediately compact the database, all tables having prime keys are re-sorted into the natural order of their prime keys. HOWEVER... even one little insertion can break up this ordering again. Physically, the new record will probably just get tacked onto the logical "tail end" of the table regardless of the order.

Therefore, neileg's advice: Use a query with an ORDER BY if order is that important. And in that, neileg is spot-on target.
 

Users who are viewing this thread

Back
Top Bottom