Append records into another database

[Dixie]

I know how to append records from one table to another in the same database,
but I need to be able to append the records from all the tables in one
database into new empty tables in another database. The tables in the
second database would have the same names as those in the first database.

Can this be done and if so how?

dixie

[Ozzone]

One table at a time.

[Lyle Fairfield]

If you are starting a new database with just the data and no other
objects such as queries, forms, reports and modules, the most efficient
way may be, from the old database, to run
SaveAsText 6, "", NameofNewDataBase
THIS WILL OVERWRITE THE NEW DATABASE IF IT ALREADY EXISTS so don't use
it unless this is exactly what you want to do (create a new db with
just the Tables indexes and relationships part of the old db).
example
SaveAsText 6, "", "db1.mdb"
(if you already have a db1.mdb it will be GONE FOREVER!)

If that's not what you want to do you can use:
CurrentDb.Execute "INSERT INTO BCustomers SELECT * FROM [C:\Documents
and Settings\Lyle Fairfield\My
Documents\Access\Northwind.mdb].[Customers]"
for each table.

If you want to program all the table names you could loop throught the
tabledefs collection, examine the msysobjects tables, or use the ADO
openschema method.

[Dixie]

Unfortunately Lyle, the database already exists and has all queries, forms,
reports and modules in place.

What I am trying to achieve is an upgrade of tables where I simply append
existing data from the older database into tables with new fields and some
altered properties in the new database.

Currently, I do this manually by importing the old tables into the new
database, then running individual append queries to append the old data onto
the new empty tables.

What I would like to be able to do is automate this to the stage that the
new database would import the new data and append it onto the empty new
tables.

dixie

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

If you are starting a new database with just the data and no other
objects such as queries, forms, reports and modules, the most efficient
way may be, from the old database, to run
SaveAsText 6, "", NameofNewDataBase
THIS WILL OVERWRITE THE NEW DATABASE IF IT ALREADY EXISTS so don't use
it unless this is exactly what you want to do (create a new db with
just the Tables indexes and relationships part of the old db).
example
SaveAsText 6, "", "db1.mdb"
(if you already have a db1.mdb it will be GONE FOREVER!)

If that's not what you want to do you can use:
CurrentDb.Execute "INSERT INTO BCustomers SELECT * FROM [C:\Documents
and Settings\Lyle Fairfield\My
Documents\Access\Northwind.mdb].[Customers]"
for each table.

If you want to program all the table names you could loop throught the
tabledefs collection, examine the msysobjects tables, or use the ADO
openschema method.