Migrating Access 2013 Autonumber fields to SQL Server tables.
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Opinion:
I'm not sure what started this practice but naming every primary key "ID" simply obfuscates relationships. If you name primary keys after their table, they are easy to spot when used as foreign keys and even someone unfamiliar with the relationships can get a sense of how tables are related.
I'm not sure what started this practice but naming every primary key "ID" simply obfuscates relationships. If you name primary keys after their table, they are easy to spot when used as foreign keys and even someone unfamiliar with the relationships can get a sense of how tables are related.
I agree, Pat.
For primary keys, we use a naming convention like:
OrderKey
CustomerKey
etc.
When primary keys appear as foreign keys, we use the exact same names. However, we make exceptions to disambiguate, for example:
EmployeeKey_ReportsTo
AddressKey_Billing
AddressKey_Shipping
TaxRateKey_Default
Cheers,
Armen Stein, Access MVP
J Street Technology
For primary keys, we use a naming convention like:
OrderKey
CustomerKey
etc.
When primary keys appear as foreign keys, we use the exact same names. However, we make exceptions to disambiguate, for example:
EmployeeKey_ReportsTo
AddressKey_Billing
AddressKey_Shipping
TaxRateKey_Default
Cheers,
Armen Stein, Access MVP
J Street Technology
ASKER