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?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
[ID] [int] IDENTITY(1,1) NOT NULL

that is an "autonumber" in SQL Server

IDENTITY [ (seed , increment) ]
...
Identity columns can be used for generating key values. The identity property on a column guarantees the following:
    Each new value is generated based on the current seed & increment.
    Each new value for a particular transaction is different from other concurrent transactions on the table.
see: IDENTITY property
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MlandaTCommented:
SSMA has a Keep identity setting

It specifies whether SSMA preserves Access identity (aka AutoNumber) values when it adds data to SQL Server. If this value is False, SQL Server assigns identity values.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access AutoNumber would not be equal to  the SQL UniqueIdentifier datatype. That's a GUID:

https://msdn.microsoft.com/en-us/library/ms187942.aspx?f=255&MSPPError=-2147217396

The equivalant would be the Identity type, as Paul Maxwell has indicated.

If you're using the Migration Assistant, it should have converted those to the Identity type (assuming you didn't change it, of course). You can check your tables after they're created in SQL to insure they are set as Identity - open the table in Design view, and check the Identity property of your ID column - it should read YES.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlcktmguyAuthor Commented:
Thanks for the clarification and additional information.
0
PatHartmanCommented:
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.
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.