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]

Re: Append records into another database

One table at a time.

[Lyle Fairfield]

Re: Append records into another database

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, "", NameofNewDataBa se
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.Execu te "INSERT INTO BCustomers SELECT * FROM [C:\Documents
and Settings\Lyle Fairfield\My
Documents\Acces s\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]

Re: Append records into another database

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" <lylefairfield@ aim.com> wrote in message
news:1138542385 .238898.116640@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> 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, "", NameofNewDataBa se
> 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.Execu te "INSERT INTO BCustomers SELECT * FROM [C:\Documents
> and Settings\Lyle Fairfield\My
> Documents\Acces s\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.
>[/color]