A client is outgrowing the Access 2013 (32bit) DB size restriction so I want to migrate to a SQL Server backend.
There are many, many table to be converted. I have tried pulling the tables to a SQl Db using SQL Server Migration Assistant for Access (32bit). Generally the table pulls correctly converting the Access data types to SQL data types and retaining the data.
However, I am having an issue with the autonumber fields.
As background, in every table I define the first field is ‘ID’. They type is set to autonumber and I also set it as the primary key for the table.
When the tables are pulled into SQL the ID filed is converted to type ‘int’.
The primary key specification is kept.
I noticed this when I tried to add new record to the table and got a duplicate error.
In SQL design mode, I tried changing the type of the SQL ‘ID’ field to ‘uniqueidentifier’ but I get an error, ‘Conversion From ‘int’ to ‘uniqueidentifier’ is not supported on the connected database server.
When I look in the script to create the table the first lines are
CREATE TABLE [dbo].[tblProperty](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Muni] [nvarchar](3) NULL,
I thought maybe the issue was that there was already data in the table so I tried everything again for another table that I had removed the result of. Same issue.
I looked in the mapping options in SSMA from Access field type to SQL field type and there is no option to convert an autonumber field.
Question: How are you migrating your Access 2013 tables to SQL so the autonumber fields are converted properly?