Recordset Order and Autonumber

[chitown05]

I have a Database with a large amount of data, I had an autonumber as the
primary key. Over time I have deleted records and added many more. Recently
I have been adding records and when I use controls to move through the
recordset the order of the primary key is off. It seems like its backfilling
an array. I tried to export the table and reimport it that solved the order
issue but I lost the autonumber feature. Is there any way to solve the
problem without losing the autonumber on my primary key. I am working with
Access 2000
Thanks

 

[Tim Ferguson]

I have a Database with a large amount of data, I had an autonumber as
the primary key. Over time I have deleted records and added many
more. Recently I have been adding records and when I use controls to
move through the recordset the order of the primary key is off. It
seems like its backfilling an array.

I don't understand this. Is this an incrementing autonumber repeating
numbers it has used before? If so, this is a well-documented bug in early
versions of Acess 2000, and is completely fixable by updating to the
latest service pack.

On the other hand, if the records are correctly numbered, but happen to
come up in your form in a jumbled order, then it's because you have not
specified a sort order. Recordsets are commonly returned in PK order, but
there is no rule that says they have to be, and once a database has
become significantly fragmented then it's likely that the engine will
serve things up page-by-page unless you make it do different. Remember
that tables are bags of unsorted records, not streams! Set your form's
recordsource to a proper query like

SELECT This, That, TheOther
FROM MyTable
ORDER BY This

and all will be hunky-dory.

Hope that helps


Tim F

 

[Allen Browne]

If Access is not presenting the records in primary key order, check:
- that the AutoNumber field properties in table design show Sequential;
- that there is nothing in the OrderBy property of the form;
- that the form is based directly on the table.
(If it is a query, specify the sorting in the query.)

If that does not solve the problem, it may be a corrupt index. Try repairing
the database:
Tools | Database Utilities | Repair.

If Access is actually assigning primary key values that are wild, see:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

BTW, you can create a new table with an AutoNumber field, and import the
existing records (using an Append query.)

 

[chitown05]

Thanks a bunch it was the first solution with repairing the database.

 

[Tim Ferguson]

If that does not solve the problem, it may be a corrupt index. Try
repairing the database:
Tools | Database Utilities | Repair.

The OP says that this fixed the "problem". Is this because the index was
actually corrupt, or because the records were fragmented and have now been
re-written in index order? IOW will it come back when he has created and
deleted a whole lot more?

B Wishes


Tim F

 

[Allen Browne]

Access (like all other database software) uses indexes for fast record
retrieval, and it keeps the indexes in memory. If the power is interrupted
after the record has been written to disk but before Access eventually gets
round to writing the index, then the disk index and disk data don't match.

This problem is not uncommon, so all decent database software provides a way
for you to rebuild the indexes. In Access that's part of what the repair
routine does.

It is not likely to recur unless there is some reason why the writes are
likely to be interrupted again, such as bad power, bad hardware, bad
software (crashing computer), unstable network (where the data is coming
over a network), disk full, or users who power off without shutting down.

For more info, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

 

[Tim Ferguson]

For more info, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

Thanks Allen. I should have know you'd have a web page about it!

All the best


Tim F