Import data but not table from another Access file?

[Grahammer]

We've created a new database and need to import the data from our old
database. Both are MS Access databases and each have multiple tables. The
table structure of each table is completely different from each other, but
the actual fields of the data we need to move exist in both tables.

From what I see in the MS Access help I can only find how to import a
complete table from another Access database.

How can I import the data from table 1 of one file into various tables in
the second file?

e.g. Move the like data from the tables in FileOne to the tables in FileTwo.
(notice that the field names may not be the same between the tables)

FileOne.MDB --------
Table1
Name C20
Address C30

Table2
City C20
Phone C15

FileTwo.MDB --------
Table1
Name C20
LandPhone C15 <--data from Phone field in FileONe
CellPhone C15

Table2
Address C30
City C20
Postal C10

Thx!

 

[Dirk Goldgar]

We've created a new database and need to import the data from our old
database. Both are MS Access databases and each have multiple tables.
The table structure of each table is completely different from each
other, but the actual fields of the data we need to move exist in
both tables.

From what I see in the MS Access help I can only find how to import a
complete table from another Access database.

How can I import the data from table 1 of one file into various
tables in the second file?

e.g. Move the like data from the tables in FileOne to the tables in
FileTwo. (notice that the field names may not be the same between the
tables)

FileOne.MDB --------
Table1
Name C20
Address C30

Table2
City C20
Phone C15

FileTwo.MDB --------
Table1
Name C20
LandPhone C15 <--data from Phone field in FileONe
CellPhone C15

Table2
Address C30
City C20
Postal C10

Thx!

I have to hope that there are additional fields in these tables that
link related records together. Otherwise there would be no way to know
which phone numbers (in FileOne.mdb) go with which names, or which
addresses (in FileTwo.mdb) go with which names.

Assuming you do have the necessary linking fields, here's how I'd do
what you're asking. Working in FileTwo.mdb, I'd *link* to the tables in
FileOne.mdb, rather than importing them. Then I'd build and run append
queries that join those linked tables on the linking field, select the
fields I want, and append them to the appropriate fields in the FileTwo
tables.

Note, by the way, that "Name" is not a good name for a field, because
almost all objects in Access -- forms, controls, fields, etc. -- have a
Name property, so if you refer to Name in VBA code it may not be
interpreted the way you intended.

 

[Grahammer]

I have to hope that there are additional fields in these tables that
link related records together. Otherwise there would be no way to know
which phone numbers (in FileOne.mdb) go with which names, or which
addresses (in FileTwo.mdb) go with which names.

This was just a quick sample. The actual tables are much larger and more
complicated.

Assuming you do have the necessary linking fields, here's how I'd do
what you're asking. Working in FileTwo.mdb, I'd *link* to the tables in
FileOne.mdb, rather than importing them. Then I'd build and run append
queries that join those linked tables on the linking field, select the
fields I want, and append them to the appropriate fields in the FileTwo
tables.

Thanks for the quick response! I'll give it a go!

 

[Immanuel Sibero]

Hi Grahammer,

- Use the link table.
- Use Append query

From any mdb file, you can link to a table in another mdb file by using
File>>Get External Data>>Link Tables.
Once the table is linked, you can then create an Append query to append/add
data from the linked table to a table in the original mdb. In the Append
query you can also control what field goes to which field.


HTH,
Immanuel Sibero