autonumber from random to incremental

J

JT

I cannot change my autonumber from random to incremental. Any sugestions?
please help...
 
B

Banana

JT said:
I cannot change my autonumber from random to incremental. Any sugestions?
please help...

If my memory serves, you can't alter any of autonumber properties once
it's used. You would need to change the original autonumber to a plain
old number, save the table, add a new column and make it an autonumber
with incremental (which should be default anyway), then delete the old
column.

However, this is very risky move because if there are other tables
related to this table, the links will break. In such cases, consider
either using Cascade Update or if the largest number isn't that large,
insert the old values into the new autonumber.

Of course, you made a back-up, right?
 
D

Dorian

Try emptying the table then doing a compact/repair then reset the attribute.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
J

Jerry Whittle

Even if you could, autonumbers are not truely incremental. If you need
something at is incremental with no possibility of a break in the numbering,
do NOT use autonumbers. For example if you start a new record that has an
autonumber field, then change your mind, that autonumber is 'burned' and you
will have a gap. Sometimes Access will pull out an out of sequence
autonumber, including even negative numbers, for no apparent reason even when
set to incremental.

Your best bet is to do a DMax of the primary key field in the table and add
1 to it just before saving the record. For this you need to use a form and
hope that more than one person isn't inputting a new record at the same time.

Other DBMSs have things like Sequences that will truely produce an
incremental number when needed.
 
G

GBA

another approach;

make a new table with all the fields the same....(but with the autonumber
field set to increment)

and then make an append query that puts all the records into this new table...

then change table names...

you'll have to remake fixed relationships...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top