Access 2002 table changes order of entries

[Vijay]

Hello. I have a Visual Basic 6.0 program that saves data
for that program in an Access XP Database. When the user
saves a Plan (that's what we call the file), the program
makes a copy of a blank template DB, populates it with
the data, then renames it to what the user requested.
One of the DB tables, called "Fields", has two columns as
the primary key, the first is the PlanYear, which is
always an integer, with 1 as the minimum. The second is
called the FSA_ID, which can be any string. Note that
the DB is not used for any other reason than to store the
information, there are no forms, queries or reports, only
tables.
We have found that, for some Plans, the order of the
Fields (ie grassy fields, not table fields, sorry for the
confusion), changes each time the program saves and loads
a Plan. For example, in PlanYear=2, with Fields A, B, C,
D, E, ... etc, after saving and loading the Plan, the
fields are now ordered: A, P, Q, R, B, C, D, ... This
order changes each time the program saves this file.
I have been able to determine that it is definitely not
the program that changes the order. That is, the order
of the fields when running the program is the order they
are saved to the DB. If I run a query within the DB,
showing FSA_ID and PlanYear for PlanYear=2, the fields
are presented in a new order, which is what they will be
in when the Plan is loaded into the program again.
Although I am sure that the order is being changed by
Access, I cannot figure out why, or how to stop it. I
assumed that the order of records in a table would stay
exactly the way they were entered. But it isn't, and it
isn't even consistent. It doesn't happen to all (or even
most) Plans, nor does the ordering make sense, since it
changes each time the data is added to the DB. So I am
stuck. Can you help me? Thank you very much.

Vajesh Durbal

 

[Tim Ferguson]

That is, the order
of the fields when running the program is the order they
are saved to the DB. If I run a query within the DB,
showing FSA_ID and PlanYear for PlanYear=2, the fields
are presented in a new order, which is what they will be
in when the Plan is loaded into the program again.

I am afraid this is not true. Access (in fact, any database) makes no
assumptions about ordering of records, and if you don't specify a sort
order they will be presented however they get extracted by the query
optimiser. It is quite unsurprising that you'd get different sequences by
setting a filter -- in one case, they might be coming up in disk-page
order, while in the other they are probably being read from the PlayYear
index.

If you want a specific order, you must specify it yourself. Use an ORDER BY
FSA_ID clause in the sql when you open the recordset, or whatever. If you
definitely have to repeat the order in which the records were created,
you'll have to store this somehow. An Autonumber usually suffices (but does
go wrong occasionally), and another way is to add a CreateDate field that
is set to the time and date whenever a new record is created, possibly by
setting its DefaultValue to Now().

HTH


Tim F