Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Maintain Autonumber when importing data

Discussion in 'Information Technology' started by DBG, Oct 28, 2005.

  1. DBG

    DBG
    Expand Collapse
    Guest

    I have an application which we are making general database schema changes to.

    This will require data to be imported from older versions of the DB, with
    some of the fields being autonumber. How do I insert data from another
    database, maintaining the autonumber fields, and at the same time, maintain
    the ability to have that DB field continue autonumbering.

    Access complains both about inserting pre-existing data into an autonumber,
    or converting an integer field to an autonumber.

    Essentially, is there a workaround, hack, fix?

    -David
     
  2. Loading...

    Similar Threads Forum Date
    How To Maintain Chardi Kala Questions and Answers Oct 26, 2016
    Want To Maintain 100% Nutrition In Food? Cook In Earthen Pots - By Rajiv Dixit Health & Nutrition Mar 10, 2015
    India Sikhs themselves should first start maintaining dignity of turban Breaking News Aug 8, 2013
    India Maintaining peace in Punjab, our responsibility: Damdami Taksal chief Breaking News Jun 14, 2012
    Sikh Coalition Legal Victory: Sikh Prisoners Can Maintain Kesh Sikh Organisations Jun 11, 2011

  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, David.

    One may append AutoNumbered records into a table of the same structure, but
    the AutoNumbers being imported cannot duplicate the numbers already in the
    table. Remember that an AutoNumber is a Long data type, not an Integer data
    type, so the Integer field needs to be converted to the Long data type before
    the append.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.

    (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    be forwarded to me.)
    - - -
    If my answer has helped you, please sign in and answer yes to the question
    "Did this post answer your question?" at the bottom of the message, which
    adds your question and the answers to the database of answers. Remember that
    questions answered the quickest are often from those who have a history of
    rewarding the contributors who have taken the time to answer questions
    correctly.


    "DBG" wrote:

    > I have an application which we are making general database schema changes to.
    >
    > This will require data to be imported from older versions of the DB, with
    > some of the fields being autonumber. How do I insert data from another
    > database, maintaining the autonumber fields, and at the same time, maintain
    > the ability to have that DB field continue autonumbering.
    >
    > Access complains both about inserting pre-existing data into an autonumber,
    > or converting an integer field to an autonumber.
    >
    > Essentially, is there a workaround, hack, fix?
    >
    > -David
     
  4. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    '69 Camaro wrote:
    > Remember that an AutoNumber is a Long data type, not an Integer data
    > type, so the Integer field needs to be converted to the Long data type before
    > the append.


    Wouldn't all values be implicitly cast? For example (to save confusion,
    I'll use Jet 4.0 synonyms):

    CREATE TABLE Test1 (
    key_col INTEGER2 NOT NULL,
    data_col NVARCHAR(50) NOT NULL
    )
    ;
    INSERT INTO Test1 (key_col, data_col) VALUES (1, 'One')
    ;
    INSERT INTO Test1 (key_col, data_col) VALUES (2, 'Two')
    ;
    INSERT INTO Test1 (key_col, data_col) VALUES (123456, 'Illegal')
    ;

    That last line fails with an overflow, of course.

    CREATE TABLE Test2 (
    key_col INTEGER4 IDENTITY(1,1) NOT NULL,
    data_col NVARCHAR(50) NOT NULL
    )
    ;
    INSERT INTO Test2 (key_col, data_col) VALUES (123456, 'Legal')
    ;
    INSERT INTO Test2 (key_col, data_col)
    SELECT key_col, data_col FROM Test1
    ;

    All rows are successfully inserted, therefore I assume there is no
    problem with implicit casting from INTEGER2 to INTEGER4. Or have I
    missed the point?

    Also, remember that an AutoNumber can be a GUID a.k.a. replication ID
    ;-)
     
  5. DBG

    DBG
    Expand Collapse
    Guest

    I guess I should specify what I'm doing:

    Database Old has 4 records autonumbered, 1,2,3,5
    Database New is Clean

    Desired outcome, import old records into new DB, maintaining their autonumbers

    Scenario 1, I import the data, Access autonumbers them 1,2,3,4 on import
    (fail)

    Scenario 2, Set number column to Long Integer (had been doing this I
    improperly said integer), import the data, records are numbered 1,2,3,5
    When I try to change the type of the column to autonumber, Access complains
    that
    "Once you enter data in a table, you can't change the data type of any field
    to AutoNumber, even if you haven't yet added data to that field. Add a new
    field to the table, and define its data type as AutoNumber, MSO Access then
    enteres data in the AutoNumber field automatically, numbering the records
    consecutively starting with 1. [OK]"
    (fail)

    So, how to import the data, such that when I create my next record its 6.
    Or for that matter so it autonumbers.

    Thanks,

    -David

    "'69 Camaro" wrote:

    > Hi, David.
    >
    > One may append AutoNumbered records into a table of the same structure, but
    > the AutoNumbers being imported cannot duplicate the numbers already in the
    > table. Remember that an AutoNumber is a Long data type, not an Integer data
    > type, so the Integer field needs to be converted to the Long data type before
    > the append.
    >
    > HTH.
    > Gunny
    >
    > See http://www.QBuilt.com for all your database needs.
    > See http://www.Access.QBuilt.com for Microsoft Access tips.
    >
    > (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    > be forwarded to me.)
    > - - -
    > If my answer has helped you, please sign in and answer yes to the question
    > "Did this post answer your question?" at the bottom of the message, which
    > adds your question and the answers to the database of answers. Remember that
    > questions answered the quickest are often from those who have a history of
    > rewarding the contributors who have taken the time to answer questions
    > correctly.
    >
    >
    > "DBG" wrote:
    >
    > > I have an application which we are making general database schema changes to.
    > >
    > > This will require data to be imported from older versions of the DB, with
    > > some of the fields being autonumber. How do I insert data from another
    > > database, maintaining the autonumber fields, and at the same time, maintain
    > > the ability to have that DB field continue autonumbering.
    > >
    > > Access complains both about inserting pre-existing data into an autonumber,
    > > or converting an integer field to an autonumber.
    > >
    > > Essentially, is there a workaround, hack, fix?
    > >
    > > -David
     
  6. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    >> Remember that an AutoNumber is a Long data type, not an Integer data
    >> type, so the Integer field needs to be converted to the Long data type
    >> before
    >> the append.

    >
    > Wouldn't all values be implicitly cast?


    I was addressing David's complaint:

    "Access complains . . . about . . . converting an integer field to an
    autonumber."

    I have no idea what method David is using to convert the Integers into the
    AutoNumbers to bring about this problem, but if they are Longs, there
    shouldn't be a problem.

    > Also, remember that an AutoNumber can be a GUID a.k.a. replication ID


    Run from anyone selling this snake oil. ;-)

    Please see MichKa's (former MVP) article, "Replication and GUIDs, the Good,
    the Bad, and the Ugly," on the following Web page:

    http://www.trigeminal.com/usenet/usenet011.asp?1033

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.
     
  7. DBG

    DBG
    Expand Collapse
    Guest

    I have been perhaps (lazily, and sheepishly admit) using copy from one table
    and paste into another table. I'll give it a test with SQL and see if this
    solves my problem. I guess I mistakenly assumed that Access would handle
    this. I'll report on my findings.

    Also,
    http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

    Seems to propose an agreeable solution if SQL itself doesn't work.

    -David


    "peregenem@jetemail.net" wrote:

    >
    > '69 Camaro wrote:
    > > Remember that an AutoNumber is a Long data type, not an Integer data
    > > type, so the Integer field needs to be converted to the Long data type before
    > > the append.

    >
    > Wouldn't all values be implicitly cast? For example (to save confusion,
    > I'll use Jet 4.0 synonyms):
    >
    > CREATE TABLE Test1 (
    > key_col INTEGER2 NOT NULL,
    > data_col NVARCHAR(50) NOT NULL
    > )
    > ;
    > INSERT INTO Test1 (key_col, data_col) VALUES (1, 'One')
    > ;
    > INSERT INTO Test1 (key_col, data_col) VALUES (2, 'Two')
    > ;
    > INSERT INTO Test1 (key_col, data_col) VALUES (123456, 'Illegal')
    > ;
    >
    > That last line fails with an overflow, of course.
    >
    > CREATE TABLE Test2 (
    > key_col INTEGER4 IDENTITY(1,1) NOT NULL,
    > data_col NVARCHAR(50) NOT NULL
    > )
    > ;
    > INSERT INTO Test2 (key_col, data_col) VALUES (123456, 'Legal')
    > ;
    > INSERT INTO Test2 (key_col, data_col)
    > SELECT key_col, data_col FROM Test1
    > ;
    >
    > All rows are successfully inserted, therefore I assume there is no
    > problem with implicit casting from INTEGER2 to INTEGER4. Or have I
    > missed the point?
    >
    > Also, remember that an AutoNumber can be a GUID a.k.a. replication ID
    > ;-)
    >
    >
     

Share This Page