Having trouble formulating TransferDatabase command in VBA.

[KmhComputer]

Hi,

Thanks for helping.

I have two Access Mdb files. Every once in a while, a function needs to run
that will take tabes from one Mdb and copy them into another. One catch is
the table names in the two Mdb files are different as well as their layouts.
I have a procedure that goes through and puts the data from the first Mdb
file in the correct form etc for second Mdb file. But then I'm not sure how
to use the TransferDatabase Command to actually move the data from the Mdb
with one name to another Mdb with a different name for the table.

So I have two Mdb files which I'll label Mdb1 and Mdb2

Mdb1 has a table which I'll label Table1
Mdb2 has a table which I'll label Table2

So how do I code the following:

Export Table1 from Mdb1 to Table2 in Mdb2

Thanks so much for any help you can give,
Kathy

 

[Dirk Goldgar]

Hi,

Thanks for helping.

I have two Access Mdb files. Every once in a while, a function needs
to run that will take tabes from one Mdb and copy them into another.
One catch is the table names in the two Mdb files are different as
well as their layouts. I have a procedure that goes through and puts
the data from the first Mdb file in the correct form etc for second
Mdb file. But then I'm not sure how to use the TransferDatabase
Command to actually move the data from the Mdb with one name to
another Mdb with a different name for the table.

So I have two Mdb files which I'll label Mdb1 and Mdb2

Mdb1 has a table which I'll label Table1
Mdb2 has a table which I'll label Table2

So how do I code the following:

Export Table1 from Mdb1 to Table2 in Mdb2

Thanks so much for any help you can give,

The thing is, you want to import (or export, depending on where you are
standing) the *data*, not the table; that is, you want to copy the
records from Table1 into Table2, but you don't want to replace Table2
completely or create a new table in Mdb2. Is that right?

Will you be running the code in Mdb1, to export to the Mdb2? Or was it
the other way around: running in Mdb2 to import the data from Mdb1?
You can use TransferDatabase to link to the table in the other database,
and then run an append query to copy records from Table1 to Table2. Or
you can even avoid linking the table by writing an append query that
uses the IN clause to specify a different source database.

 

[KmhComputer]

Thanks for getting back to me so quickly.

I'm trying to export actual tables from MDB1 to MDB2.

I have a long function that currently goes through MDB1 and puts the data I
need in the format I need it to be in for MDB2 and stores this information in
temporary tables in MDB1.

I then need to export these temporary tables from MDB1 to MDB2 and rename
these tables in the process. This is actually a function that will go away
some day, so I don't need to actually link the two MDB files.


Kathy

 

[Dirk Goldgar]

Thanks for getting back to me so quickly.

I'm trying to export actual tables from MDB1 to MDB2.

I have a long function that currently goes through MDB1 and puts the
data I need in the format I need it to be in for MDB2 and stores this
information in temporary tables in MDB1.

I then need to export these temporary tables from MDB1 to MDB2 and
rename these tables in the process. This is actually a function that
will go away some day, so I don't need to actually link the two MDB
files.

Oh! Well, then, it's a lot simpler than I thought. To simply export
table MDB1.TABLE1 to MDB2.TABLE2, you should be be able to use
TransferDatabase as you originally thought, using syntax like this:

DoCmd.TransferDatabase _
acExport, _
"Microsoft Access", _
"C:\Path To\MDB2.mdb", _
acTable, _
"Table1", _
"Table2"

In the above, you would need to use the full path to the target
database, MDB2, where I put "C:\Path To\MDB2.mdb".

 

[KmhComputer]

Hi Dirk,

Thanks so much! Works like a charm! I really appreciate the help,

Kathy