CopyingTables from one db to another from third db

[WSF]

Access97

Is it possible via code to copy a table from say,
mdb1 to
mdb2
using code in mdb3


Thanks in advance.

WSF

 

[Larry Linson]

Check Help for CopyObject and TransferDatabase.

 

[WSF]

It seems these options are only for working data to/from the current
database.

What I would like to do working from say DB1.mdb

is take a table "Stock" in DB2.mdb and copy it (using same name) to a third
database named DB3.mdb.

I want to be able to do this remotely without actually having to open DB2 or
DB3.

Is this possible?

WSF

 

[John Spencer (MVP)]

Kind of.

Copy from dDB2 to DB1
Copy to Db3 from DB1
Delete the copy in DB1

But you can't copy directly (as far as I know).

 

[Chadlon]

How about automation?

DB1 fires up an Access session, opens DB2 (or DB3) and runs the appropriate
transfer command to or from the other db.

If the objective is to keep all the 'intelligence' in DB1, then that should
do it??

CD

 

[Dirk Goldgar]

How about automation?

DB1 fires up an Access session, opens DB2 (or DB3) and runs the
appropriate transfer command to or from the other db.

If the objective is to keep all the 'intelligence' in DB1, then that
should do it??

I'm quite sure that would work. Another alternative is to use DAO with
two open database objects. Microsoft has a KB article with code for
copying a table from the current db to a target db via DAO; one would
just have to adapt the code to use a different source db. I don't know
the article number offhand, though I have a copy of the code and can
post it of requested. Or one could search the KnowledgeBase for
"CopyTableDef".

 

[Chadlon]

Just as an exercise, I ran a test - worked fine:

Sub threeDBcopy()
Dim objAcc As Access.Application
Dim DB2_Name$, tableInDB2$
Dim DB3_Name$, tableInDB3$
Set objAcc = CreateObject("Access.Application.9")
DB2_Name$ = "F:\DbData2000\xxxx.mdb"
DB3_Name$ = "F:\DbData2000\yyyy.mdb"
tableInDB2$ = "Works Order"
tableInDB3$ = "Works Order"
objAcc.OpenCurrentDatabase DB2_Name$
objAcc.DoCmd.TransferDatabase acExport, "Microsoft Access", DB3_Name$,
acTable, tableInDB2$, tableInDB3$
objAcc.Quit
Set objAcc = Nothing
End Sub

 

[WSF]

Thank you all very much for the tips.
I will work on it over the next few days and let you know.

The help is appreciated.
Regards,
Bill Fraser