Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Australia
    Posts
    217

    Unanswered: How to Keep Autonumber when transferring data ?

    I want to transfer some data from one SQL server database to another, but there is an Autonumber field.
    The front-end is MS Access.
    I want to keep the Autonumber value, I don't want new Autonumber values to be created.

    Somebody told me Append Query will do the job. But when I tried, error message appeared saying "X records cannot be appended due to key violation".
    But I can't see any no key violation.

    Does anybody help ? Thanks.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Insufficient information.

    Front end is completely irrelevant.

    My suggestion is to backup the database and restore it on the other.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If for some reason you cannot backup/restore, I'd suggest you don't make the SQL Server field an identity field (ie. autonumber) until after you've appended the data. Then change it to an identity field.

    But the error you indicated could also be due to other reasons (ie. you violated the primary key constraint (tried creating duplicates or blank values) or you violated the relational structure by not appending data to the main table in the relational structure first.)
    Last edited by pkstormy; 11-04-09 at 21:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    you are discussing appending table data and the backend you say is sqlserver - - so this doesn't really below in the Access forum

    autonumber is a table field property; and if the field property also is indexed/No duplicates then you can not merge/append tables from another table that have an autonumber that is a duplicate to any existing field.

    you can do an append query; but leave out the autonumber field - and when it appends it will generate all new autonumbers....having said that - it may be very important that you keep the existing autonumber values from the original table (because they are cross referencing key values to other tables)...if so - your problem is that in your table merge you appearantly will have two rows with the same value... the error message you receive is saving you from that... the field has been defined as not allowing duplicates...and probably there is a very good reason for this....so you need to decide as to whether to change the values of one table or to allow duplicates....

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm keeping it in this forum only because it will be useful to others who utilize MSAccess and this technique.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •