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?
please try to download the latest BETA version (requires trial or commercial key). The AutonumberLI should be converted to Long Integer in child table and AutonumberRID should be converted to Replication ID data type in Access 2007-2010 models.
I downloaded 5.0.1.3 beta and tried it, but I’m having the same issue. I end up with a single table and two autonumber fields. Not sure if it makes a difference, but I’m using a domain to set the autonumber field.
In any event, I still end up having to edit the resulting VBA to get this to run properly.
I will discuss it with our Access specialist tomorrow. A simple workaround is to not use the domain. If you set the data type for your parent column as autonumber directly, it will migrate to child table correctly and the tool will do the automatic data type conversion for you.
For now I’m just editing the VBA that gets generated. I use domains all over the place as I find they really save me time when a database gets large. I’d much rather use domains and have the autonumber field properly translate the field type. Any help you can provide would be greatly appreciated.
in the current version the data types used in domains cannot be automatically converted to different data types in child entities. I created new change request for this: CR#108039, status “Open”.