Populating an autonumber field

Discussion in 'Microsoft Access External Data' started by GPO, Sep 16, 2009.

  1. GPO

    GPO Guest

    Access 2003
    I think I may have gotten myself into a spot of bother here. I have an
    access db that has four transactional tables and a series of reference data
    (or lookup) tables. The PKs in all tables are autonumbers. There are numerous
    relationships between the tables (where the PK autonumber joins to a long int
    FK).

    The live copy of the database is in a production environment, so when I need
    to faff around with anything, I use a copy in a development environment.
    People are free to continue using the live database while I'm working on the
    dev copy, so before I migrate the dev copy back into production, I want to
    empty the four transactional tables on the dev copy, and import the live
    data, then copy the dev db over to the production envioronment. Problem is I
    can't work out how to migrate the live data into the test copy because I have
    to preserve the autonumber values. I can't just let access create a whole new
    bunch of autonumbers because all the PK-FK relationships wil be broken.

    I thought about dropping the tables in the dev copy and importing the live
    ones (using TransferDatabase), but this appears to involve killing all the
    PK-FK relationships and then re-eatablishing them, which would be an awful
    lot of work.

    Is there really no way to insert data while preserving the autonumber values?
     
    GPO, Sep 16, 2009
    #1
    1. Advertisements

  2. GPO

    GPO Guest

    Sorry. It IS possible to populate the autonumber field using an INSERT
    statement. (blush)

    ;-)

    "GPO" wrote:

    > Access 2003
    > I think I may have gotten myself into a spot of bother here. I have an
    > access db that has four transactional tables and a series of reference data
    > (or lookup) tables. The PKs in all tables are autonumbers. There are numerous
    > relationships between the tables (where the PK autonumber joins to a long int
    > FK).
    >
    > The live copy of the database is in a production environment, so when I need
    > to faff around with anything, I use a copy in a development environment.
    > People are free to continue using the live database while I'm working on the
    > dev copy, so before I migrate the dev copy back into production, I want to
    > empty the four transactional tables on the dev copy, and import the live
    > data, then copy the dev db over to the production envioronment. Problem is I
    > can't work out how to migrate the live data into the test copy because I have
    > to preserve the autonumber values. I can't just let access create a whole new
    > bunch of autonumbers because all the PK-FK relationships wil be broken.
    >
    > I thought about dropping the tables in the dev copy and importing the live
    > ones (using TransferDatabase), but this appears to involve killing all the
    > PK-FK relationships and then re-eatablishing them, which would be an awful
    > lot of work.
    >
    > Is there really no way to insert data while preserving the autonumber values?
     
    GPO, Sep 16, 2009
    #2
    1. Advertisements

  3. You need to split the database into a Front End with all the forms, reports,
    etc., and a Back End with just the tables. That way you can make changes to
    the FE and just replacing the existing FE. Then you don't have to worry about
    migrating data.

    Now if you do need to update the tables, the trick here is to practice on a
    copy of the BE and write down detailed notes on what you need to do including
    any SQL statements to modify existing data. Then kick everyone off the
    database (or work weekends and evenings - such is the life of a DBA), then
    modify the production BE.

    If you are making massive, frequent changes to the tables, well, then your
    database probably isn't ready for prime time and shouldn't be in production
    in the first place.
    --
    Jerry Whittle, Microsoft Access MVP
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "GPO" wrote:

    > Sorry. It IS possible to populate the autonumber field using an INSERT
    > statement. (blush)
    >
    > ;-)
    >
    > "GPO" wrote:
    >
    > > Access 2003
    > > I think I may have gotten myself into a spot of bother here. I have an
    > > access db that has four transactional tables and a series of reference data
    > > (or lookup) tables. The PKs in all tables are autonumbers. There are numerous
    > > relationships between the tables (where the PK autonumber joins to a long int
    > > FK).
    > >
    > > The live copy of the database is in a production environment, so when I need
    > > to faff around with anything, I use a copy in a development environment.
    > > People are free to continue using the live database while I'm working on the
    > > dev copy, so before I migrate the dev copy back into production, I want to
    > > empty the four transactional tables on the dev copy, and import the live
    > > data, then copy the dev db over to the production envioronment. Problem is I
    > > can't work out how to migrate the live data into the test copy because I have
    > > to preserve the autonumber values. I can't just let access create a whole new
    > > bunch of autonumbers because all the PK-FK relationships wil be broken.
    > >
    > > I thought about dropping the tables in the dev copy and importing the live
    > > ones (using TransferDatabase), but this appears to involve killing all the
    > > PK-FK relationships and then re-eatablishing them, which would be an awful
    > > lot of work.
    > >
    > > Is there really no way to insert data while preserving the autonumber values?
     
    Jerry Whittle, Sep 16, 2009
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt

    Can't import with autonumber field

    Matt, Jun 25, 2003, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    4,201
    GVaught
    Jun 26, 2003
  2. Brett

    Appending records with Autonumber field...

    Brett, Jul 28, 2003, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    530
    Brett
    Jul 28, 2003
  3. Bakema

    Populating a Word field from an Access form

    Bakema, Apr 8, 2004, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    289
    Tony C
    Apr 8, 2004
  4. Colin Basterfield

    Resetting AutoNumber field

    Colin Basterfield, Jun 16, 2004, in forum: Microsoft Access External Data
    Replies:
    4
    Views:
    256
    Colin Basterfield
    Jun 16, 2004
  5. Guest

    Autonumber Field Increments Incorrectly When Importing from Excel

    Guest, Dec 21, 2004, in forum: Microsoft Access External Data
    Replies:
    2
    Views:
    440
    Jeff Boyce
    Dec 24, 2004
Loading...

Share This Page