Autonumber question

[Laurel]

I have a young application, and now and again it is necessary to modify the
table definitions. The problem I face is that if I copy the live data into
the new (empty) table, the autonumber columns increment in the destination
table. I tried changing the datatype to text, planning to change it back
after it was done. I can do that in the destination table, but not in the
source table, because you can't change the datatype of an autonumber column
if there is data in it.

This must be a common problem. How do other people handle it?

 

[Damon Heron]

In table design, remove primary key from autonumber, then change to number.
Add a new field, set to autonumber, set as PK, then save. Open table. You
now will have a list, beginning with 1, of autonumbers. Go back to table
design and delete your old number field, and drag the new autonumber field
to top if you want.
See also Access help regarding changing the starting value of an autonumber
field, for instance, if you wanted to start with 1000.

HTH,
Damon

 

[Rolls]

I have a question about your design methods if you're changing tables ID #s
once they're set up. If you're using entity tables which contain unique
information (could be multiple fields) associated with an ID, ex:

tblPeople
PersonID
Title
FName
MName
LName
Suffix

why would you ever have to change an ID #? You would just be adding new
records, deleting old records, or updating a single Mary Smith to a married
Mary Jones.

Same for relationship tables containing only FK associated with the PK IDs
in the entity tables.

Something's amiss here ...

 

[John Vinson]

How do other people handle it?

By ignoring the value in autonumbers. It's meaningless and arbitrary;
if you have a field that you want to be meaningful, sequential, and
without gaps, then an Autonumber is just the wrong type of field.

 

[Laurel]

My original post is no longer available to me. (Not sure when/why this
happens). But I explained there that infrequently I need to change the
table definitions of a live application. To do that, I need to re-import
the live data after I've changed the structure. The problem arises for
precisely the reason you're talking about. I can't aford to have the ID
numbers change, but they do. When I import, the start at the last number
used for that table, even though I deleted all the original data before
copying in the live data.

Unfortunately, I see, too, that the helpful answer I got from someone is
also no longer visible....

I'm not sure if this is Outlook Express or the Newsgroup..

 

[Cheryl Fischer]

Hi Laurel,

Did you know that older threads from this and other newsgroups are archived
at: http://groups.google.com ? It is very handy. Anyway, the thread
that I think you are referring to can be found at:

http://tinyurl.com/2hlmm

 

[John Vinson]

My original post is no longer available to me. (Not sure when/why this
happens). But I explained there that infrequently I need to change the
table definitions of a live application. To do that, I need to re-import
the live data after I've changed the structure. The problem arises for
precisely the reason you're talking about. I can't aford to have the ID
numbers change, but they do. When I import, the start at the last number
used for that table, even though I deleted all the original data before
copying in the live data.

You should be able to run an Append query appending ALL the fields -
*including the Autonumber field* - into the new table. If you Append a
numeric value into an Autonumber field, it will accept the value from
the append query, rather than autonumbering.

If this isn't happening, please post the table structures and the SQL
view of your append query. Something's odd!