New data, but keep relationships

[durexlw]

You know the scenario where people keep working in an old version of a database, while you work on an update... leaving you with a new database, but old data?

This concerns a database, containing about 20-25 tables all linked together.
I was asked to make an update. In this update 3 new tables were included and here and there a few of the old tables got news fields, nothing crucial and a lot of forms were changed.

The current situation:
Database 1: contains new data, misses a few tables, is missing some fields, forms and queries
Database 1.1: contains old data, but some new extra tables, added fields, forms and queries

I would like to know if it is possible to import all data from 'database 1' into 'database 1.1', so that all linked information between the data is preserved, all database relations are maintained.

If I import tables from Database 1 into database 1.1, tables just get added, not replaced. If I delete or rename tables, I loose my relationships.

I would like to know what is a common and effective way to get all the new data of an old version of a database (where people keep working in while you work on an update), into the new version of the database, that currently contains old data.

 

[WayneRyan]

d,

On the surface:

1) Make a new blank database (This preserves database 1 and database 1.1)

2) Import the tables/forms/queries from database 1.1 (Now you have the relations,
table structures and s/w)

3) Delete the data from the tables

4) "Link" to the tables in Database 1, it doesn't matter if the tablenames have a "1" at
the end.

5) Starting with the parent tables (as per your relations in the 1.1 design), make a query
to copy the data from database 1 to database 1.1

6) When finished, drop the "linked" tables.

Insert Into SomeTable (OldField1, OldField2, OldField3, NewField1)
Select OldField1, OldField2, OldField3, 'SomeDefaultForTheNewField'
From   SomeTableLinked

If you have AutoNumber Fields that defined the relationships between your database 1
tables, then you'll have to preserve them as "Long" fields and use it to retrieve your
"New" autonumber in database 1.1

See, that wasn't too bad was it?

Wayne

 

[gemma-the-husky]

you dont need to import the data

just copy the database to a new file name, in windows

dont bother about deleting stuff you dont want any nore - just add the new stuff

 

[durexlw]

Thanks gemma-the-husky and WayneRyan... I couldn't have asked for more.

What you said gemma, is usefull indeed, however, I did this and when I imported the three new tables, they seemed to have lost their relationship (when I looked it table relationships, all relations between the imported tables are gone). This will work for three tables, but I wouldn't want to be the guy that needs to import 50 tables and redo all relationships, so I was wondering if there were better ideas so I didn't need to re-establish the relation for every table I import.

What you said WayneRyan, is what I suspected that there had to be a way to get the data from the 'inside' while keeping the outside table and it's relations, in stead of replacing the table with the data in it.
Especially what you said about the 'making autofields' Longs before importing is the part I was missing. Thanks for pointing this out man, it all makes sense now.

Thanks you both, this is what I call the best of two worlds. It makes me hopeful to know I have a choice now.

Cheers,
Andy