How can I get round identity issues with Importing Access data into SQL Server

I am figuring out how to migrate my data from an Access database to a SQL Server (2008 R2 Express Edition) one. I was assuming that using Task->Import and loading data that way w开发者_运维百科ould be the way to go, but I have just hit a gotcha.

The data in the tables I am importing have autoincrement keys set, which I have to maintain as I load the data. This is because there are (not enforced) foreign key references to the values. I have now figured out that I have to create the tables within SQL Server with the IDENTITY option set and then use SET IDENTITY_INSERT to ON for the table before importing. Experiments show that the script (which I can't see) fails if this is required.

I have tried an experiment to create do something manually to display table "Prospect" the first attempt being

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Datasource="C:\Users\Alan\Documents\db\backend.mdb";Jet OLEDB:System Database="C:\Users\Alan\Documents\db\Secured.mdw";User ID=Alan;Password=xxxxxx;')...Prospect;

but this failed with the following

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Having found statements on the internet that suggested that this may be overcome using a different driver, I naively tried

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Datasource="C:\Users\Alan\Documents\db\beckendn.mdb";Jet OLEDB:System Database="C:\Users\Alan\Documents\db\Secured.mdw";User ID=Alan;Password=xxxxxx;')...Prospect; 

but this also failed with ...

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Can someone tell me what I am doing wrong

[Note: Whilst double checking this message I tried to import the table again into a fresh database and it failed to copy an entry. It looks like an Access field of type Byte, with a non zero value in it (the copied table contains lots of entries with zero, but none of the non zero ones) to a field of type smallint caused an overflow error]

Try using the free software tool called the SQL Server Migration Assistant from Microsoft: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5abe098d-c7e1-46c6-994a-09a2856eef0b&displaylang=en