Adding an AutoNumber field to a pre-existing table? 1

[dandyboy]

I had this huge table which I divided up into 4 smaller tables to make this more manageable. However, the autonumber field still has the same row numbers as it did in the huge table. I played with it awhile to see if I could get that field to start at one, but I couldn't.
I tried to insert another field in design view and then define it as autonumber but I got message boxes popping out saying that ACCESS won't allow that.

 

[Guest_imported]

Hi,
When you add a record the autonumber is set to 1 and on addition of records it is incremented by 1.
Say, u have 10 records. Now if you delete all the records then next addition will set the autonumber to 11. That's the way it works. I think u can't reset it.
Try coding in the front end to increment a integer (by selecting the max of that int and increment result by 1).

Further I think u can't make the autonumber a Primary Key

 

[Betsvigi9]

I've had this problem before and this is what I did:

I changed the autonumber field to a number field, if its a field you are using as a joined field then you will have to delete that join first. Change the name of that field and then create another autonumber field giving the same name as the original. Then recreate the relationships you previously deleted. If you want the same autonumber in each of the four tables then you will have to do the same thing for each of them, making sure that they are all in the same order before you start, of course.

Hope that solves what you were trying to do.

Jude

 

[thornmastr]

copy your table to another table.

from the original table delete all records.

compact database.

create update query to update your original table with all values from saved table but not the primary key. your autonumber will now start with 1. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com