I have records in two different mdb files. I want to combine them into one
file or at least transfer the records from one DB to another. How can i do
this without doing it manually record by record?
This can be easy - or it can be surprisingly difficult. Do either of
your tables have Autonumbers? If so, you almost surely have records in
the two tables with the same value of the Autonumber. Do you have
multiple tables, with relationships to an Autonumber primary key? If
so, you have to consider updating these tables as well! Or, do you
have duplicate data in the two tables, or (even worse) *almost*
duplicate data (like William Jones in one database and Bill Jones in
the other - are they the same person, or father and son, or unrelated
people?)
If it's a simple case with just one table in each .mdb file, with the
same field definitions, and you don't care what the Autonumber value
is (or you don't have an Autonumber), you can do this pretty easily.
First, MAKE A BACKUP of each of your databases! Open one database (the
"target" database which will end up containing both databases' data)
and select File... Get External Data... Link from the menu. You'll get
a Windows file search menu; find the other database (the "source"),
doubleclick it, and select the table containing the data that you want
to add.
If the tables have the same name, you'll now have Tablename (your
local table) and Tablename1 (the linked table). If they're named
differently, you'll have Tweedledum (the local table) and Tweedledee
(the linked table).
Create a new Query based on the linked table; change it to an Append
query using the query-type icon or the Query menu item. You'll be
asked which table you want to append to; select the local table
(Tweedledum or Tablename).
If the fieldnames match, Access should fill them in automatically; if
not, select the desired target field for each source field.
If you have an Autonumber field in the target table, do NOT append
anything into it (otherwise you'll probably get duplicates) - just
leave it out of the query, and you'll get new autonumber values when
you append the data.
Run the query by clicking the ! icon (you *DID* check that your two
databases are backed up... right!?) and accepting Access' warning that
you will make irreversible changes to your data.
If you DO have related tables, or if you have (unwisely) assigned
significance to the autonumber values, post back with a more detailed
description. This can be done, it's just sometimes a real hassle!
John W. Vinson[MVP]
