By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

RE-Indexing a MS Access database

P: n/a
All

I have a database that I am taking over the maintenance of - the
primary index in this relational database is a field JP_ID which was
defined as an AUTO NUMBER type field.........now the problem is that
there are huge gaps in the JP_ID field and the number has even gon
negative (e.g. -144456766678)

There are subordinate tables that use the JP_ID.......

QUESTION: Is there a way to re-index the primary table and then send
those new indexes down to the subordinate table??? Is there a package
or script??

Thanks!!

Aug 11 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
rabb...@anderson4.k12.sc.us wrote:
All

I have a database that I am taking over the maintenance of - the
primary index in this relational database is a field JP_ID which was
defined as an AUTO NUMBER type field.........now the problem is that
there are huge gaps in the JP_ID field and the number has even gon
negative (e.g. -144456766678)

There are subordinate tables that use the JP_ID.......

QUESTION: Is there a way to re-index the primary table and then send
those new indexes down to the subordinate table??? Is there a package
or script??
A skillful and knowledgeable Access developer could write code for this
in an hour or so, given a throrough knowledge of the schema, but the
code's procedures are likely to destroy the integrity of the data if
they are not implemented carefully by someone who would not have to ask
the question.

I would not be confident enough of my communicating ability to walk a
colleague through this, much less an unknown from the net; but perhaps
others are.

One of the Access Utility Websites may have something available for
this.

Of course, if the numbers have some real-world meaning then they should
not be changed. If they don't, it matters nothing that they run across
the entire range of the longs. If people who see them are alarmed, hide
them, or find some vodka coolers and deliver one or two to each of
their desks.

Aug 11 '06 #2

P: n/a
As AutoNumbers are for internal use of the application not for display to
users, for use in joins and surrogate keys, and are not intended to be
sequential (though they often are when "originally issued", but not
necessarily) but only unique, why would you feel it necessary to "close the
gaps"? I suggest you heed the very worthwhile solution proposed by my
colleague Lyle.

Larry Linson
Microsoft Access MVP
<ra*****@anderson4.k12.sc.uswrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
All

I have a database that I am taking over the maintenance of - the
primary index in this relational database is a field JP_ID which was
defined as an AUTO NUMBER type field.........now the problem is that
there are huge gaps in the JP_ID field and the number has even gon
negative (e.g. -144456766678)

There are subordinate tables that use the JP_ID.......

QUESTION: Is there a way to re-index the primary table and then send
those new indexes down to the subordinate table??? Is there a package
or script??

Thanks!!

Aug 11 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.