PC Review


Reply
Thread Tools Rate Thread

Populating an autonumber field

 
 
GPO
Guest
Posts: n/a
 
      16th Sep 2009
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?
 
Reply With Quote
 
 
 
 
GPO
Guest
Posts: n/a
 
      16th Sep 2009
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?

 
Reply With Quote
 
 
 
 
Jerry Whittle
Guest
Posts: n/a
 
      16th Sep 2009
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?

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
AutoNumber Field Not Populating with Append Query AccessIM Microsoft Access Queries 2 26th Feb 2010 10:26 PM
Autonumber without a autonumber field PennyB Microsoft Access VBA Modules 3 3rd Jul 2009 05:41 PM
Create a Sub Autonumber that re-assigns autonumber to each data groupin a sorted field. Junwenren Microsoft Access 1 4th Oct 2008 05:30 AM
Adding autonumber to a non autonumber field don Microsoft Access 2 22nd Apr 2008 05:00 PM
AutoNumber field does not contain "(AutoNumber)" when adding a record Keith Microsoft Access Forms 3 12th Nov 2003 05:50 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:28 PM.