I have a model that I am creating for Access 2010. I need to be able to use an Autonumber field as the data type and then use the field in a foreign key. Unfortunately this “cascades” the autonumber field to the child table, which is not expected behaviour. Not to mention, if the child table already has an autonumber field on it then you end up with a table with two autonumber fields, which won’t even get created.
In SQL Server there’s an “override identity” setting that gets around this, but I don’t see anything equivalent in Access. How do I avoid the double autonumber problem?