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?

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?

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?