Learn how to a build a cloud-first strategyRegister Now


Autonumber to Int

Posted on 2011-05-05
Medium Priority
Last Modified: 2012-05-11
I had an Access database but just converted the backend tables to SQL Server.  A number of my primary keys were autonumber in the old system but now they are integer.  How do I deal with auto-incrementing object IDs now?
Question by:gcgcit
  • 2
  • 2

Accepted Solution

cfEngineers earned 2000 total points
ID: 35701075
open sql enterprise management studio

select the table you want to modify

right click the table and choose modify or design

locate your field and click it so the arrow is pointing at it.

now below in your column properties you will see Identity Specification open that tree
select (Is Identity) to be Yes

save table


Author Comment

ID: 35701084
I found info that I need to set my indentity increment and identity seed to 1 but they are already set to that.  In the application when I'm trying to create a new record the task bar says "Cannot add record(s); primary key for table 'tblPeople" not in recordset"

Author Comment

ID: 35701098
tblPeople that I'm trying to edit is a subform, are maybe my relationships messed up?

Expert Comment

ID: 35701124
Thats possible.

Also you can right click and set to primary key
LVL 26

Expert Comment

ID: 35701479
If you still can yet, I recommend you use the (free) SQL Server Migration Assistant for MS Access

It ain't perfect (yet) but it beats the daylights out of manual conversion or the upszing wizard.
It'll analyze your db, move the tables, add the timestamp fields and create the link tables.

Fairly well-dcoumented, and google-able

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question