Copying table data from one database to another- suggestions?

[Bill]

I have a database thast has been undergoing many changes. The new version of
the database adds a bunch of forms and reports, a couple of new tables.
Now, it's time to switch over to the new version of the database. I need to
get current production information over to the new version. I'm looking for
some advice on how to make this happen- the easiest/quickest.

On the new version, the tables are out of date. I would like to just copy
the tables over from the Production version of the database to this new
updated version, but re-establishing relationships looks like a problem.
Here are a few options I've thought of:

Option 1:
One method I've tried is to import the tables from the current production
version of the database. For example, the address table- import the
production address table as address_1. Break the relationship and Delete
the existing address table, then rename address_1 to address and try to setup
the relationship again. But, when I try to re-establish the relationship
with referential integrity, I get: "can't create this relationship, data in
the table violates ref. integrity rules".
Trying to figure out what's different and causing the error will be a chore
- it happens on each table I try to go to.

Option 2:
Import the address table as address_1. Create an update query to update the
data in "address" from the "address_1" data. Looks like I'll need to do an
update, then delete query (to remove records), then append to make sure I get
the new data imported. Seems like a real pain for each table, or, am I going
down the wrong path.

Any suggestions?

 

[Jeff Boyce]

Bill

I didn't notice if you've considered only linking the tables from one db in
the other, then creating append queries (and/or update queries, depending on
your need) to move the data around. You could drop the links after the data
is migrated.

Note that no changes are needed to the relationships.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bill]

Thanks for the input Jeff. I'll look into that option. That may address
another issue too, which is:
I need to take the production database down while I import the data in the
new version database. That way, nothing changes while I'm transferring
data. If I understand linking- I could link to the data in the production
database while the production database was live? Or, is that NOT
recommended?

Because I don't need to save any data in the tables in the "newer" database,
I'm wondering if I could actually recreate all of the tables in the updated
(newer) database with a query? Start with blank tables (with relationships
established) and just append all of the data into the tables from the link?
Or - is there a more direct way without building all of the relationships?

 

[Jeff Boyce]

Bill

While not having all the details surrounding your specific situation, it
certainly sounds like a candidate for using emptied (and related) tables,
linking to Production (as it runs is fine), and using queries to load up
your new tables. You'd need to handle parent/child relationships by loading
parent records first, then their children.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[aaron.kempf]

yeah if you're rebuilding it -- then move to Access Data Projects

MDB is for lamers and retards

it's not reliable, not scalable

only retards that don't have the capacity to learn a real database use
MDB

_EVERY_MAJOR_VENDOR_ has a freeware version!

Oracle, DB2, MS SQL Server

now _WHY_ in the hell would you settle for MDB?