Hello,



I have bunch of tables linked in to Access 2007 database from MS SQL server via ODBC.

I have to provide my customer with data migration solution that would allow them to transfer the current Access data files to MS SQL databases. I have created a special Access project where there are linked in tables from the "old" Access data file, and linked tables from the "new" MS SQL database (via ODBC). I'd like to run series of queries like

INSERT INTO Table_ODBC SELECT * FROM Table_Access

In order to migrate the data. However, there are some inconsistent problems. Most of the tables contain identity PK fields, and with some (mind you, not with everyone that has identity field) Access complains that it could not insert data "due to key violations". If I'd go and manually change the MSSQL table design so that the PK field is not identity, then Access can insert the records with no problems. When I switch the identity property back, the problems reappear. Strangely, there are tables that do not suffer from this identity insertion problem, they happily take up the values. I even managed to create two tables with exactly the same design but different names (names are TV and Test). I am totally at loss with this now, could the table names really be the cause of different behaviour?