How do I compact a 2007 Access database

[Guest]

I have autonumber primary keys that have skipped sequences from deleting
records. How do I compact a 2007 Access database

 

[Larry Linson]

I have autonumber primary keys that
have skipped sequences from deleting
records. How do I compact a 2007
Access database.

Are you expecting that compacting the database will fill in the skipped
sequences? It will not, not in any version of Access, including 2007.

And, the fact that the autonumber primary keys are not strictly sequential
should be immaterial -- autonumbers are not "advertised" to be sequential,
only unique. They are for internal use in linking/joining Records, not for
displaying for the users.

Someone else, however, will have to tell you how to navigate the tabs and
ribbon to find Compact -- I don't have a spare machine that can run 2007, so
am not doing hands-on testing.

Larry Linson
Microsoft Access MVP

 

[Albert D. Kallal]

As mentioned, the next autonumber will usually have many holes...in fact if
a user starts editing a record, and then
changes their mind...a autonumber id can be skipped.

However, to compact....

Simply hit the office button (upper left).
Manage->Compact and Repair database.

 

[Nick 'The database Guy']

If you are so pedantic that you must have your key fields in
sequenctial order, this can be acheived by opening the table in design
view, deleting the field, then switching to table view so that you can
see the data. Copy all of this data, then delete it all. Save the
table. Open the table in design view add an autonumber field, save,
and paste the data. Needless to say that you will have to delete any
relationships before you delete the primary key. Furthermore you will
be unable to re-establish the links in any meaningful way as the data
will have changed irrevocably. But if your database only consists of
one table, or the tables within it in no way relate to another tables,
Go for your Life!

Good luck,

 

[Joseph Meehan]

I have autonumber primary keys that have skipped sequences from
deleting records. How do I compact a 2007 Access database

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.