auto-number not used on insert

[DKP]

I've got 2 tables A and B, where the structure of B is a copy of A. I use B as a temporary table to hold data while importing and once I know that the entire import is successful, I want to append B to A. My command for doing the append is as follows

INSERT INTO A (two, three, four) SELECT two, three, four FROM B

A and B have another field, one, which is an auto-number



The append works fine, except that the 'one' values from B are also getting used for A. Does anyone know why A wouldn't be creating its own auto-number here given this syntax? It seems like this will lead to a duplicate values of 'one' in A.

[June7]

Since the field is not included in the INSERT action, table A should generate its own values. Are you sure table A is not generating the values and they just happen to be the same as the ones in table B? Maybe remove the autonumber field from table B (apparently it is not needed) and see what happens.

[DKP]

It appears you're right. Even w/ the auto-number field removed from table B the 'one' field is still small. It's a little concerning because the original 'one' fields start at like 2500 and go to 20000, whereas the new ones are starting at around 0. Although it will take some time, the new fields will eventually cross the lower threshold of the old ones. Hope Access is smart enough to realize that and pick the next available value.

[June7]

Is the One field value saved as a foreign key in a related table?

Have you run Compact & Repair?

[DKP]

Actually, it appears that the original database developer didn't establish the relationship between those tables. I'll add it. Will that actually affect the way the auto-numbers are generated? I'll try to run compact & repair after I make those changes.

[June7]

No, relationships in the Relationship Builder are not required for a db to function.

Why establish a relationship to a 'temp' table anyway?