Copying an Object to Another DB

[LarryP]

I have a process where I copy a number of tables from one database to
another. Following is the VBA for one of those tables -- it works fine:

DoCmd.CopyObject "\\apfs\projs\prodcntl_ops\Entrpris\Data\PRISM\MSAccess
Tools\PCMetrics Tool.mdb", , acTable, "tblPCData"

Now I want to run that table through a select query in order to change some
of the field names, and copy the output of the query to the other database as
a table. I've tried several variations, but none works:

DoCmd.CopyObject "\\apfs\projs\prodcntl_ops\Entrpris\Data\PRISM\MSAccess
Tools\PCMetrics Tool.mdb", "tblPCDataA", acTable, "qryPCDataForFE"
{{{“the CopyObject action was cancelled….â€}}}

DoCmd.CopyObject "\\apfs\projs\prodcntl_ops\Entrpris\Data\PRISM\MSAccess
Tools\PCMetrics Tool.mdb", "tblPCDataA", acQuery, "qryPCDataForFE"
{{{copies it, but as a query, not a table}}}


DoCmd.CopyObject "\\apfs\projs\prodcntl_ops\Entrpris\Data\PRISM\MSAccess
Tools\PCMetrics Tool.mdb", "tblPCDataA", , "qryPCDataForFE"
{{{“the Object Type argument for the action or method is blank or
invalid….â€}}}

Help, please?

 

[Douglas J. Steele]

You cannot transfer data from a query to a table that way. You'll have to
create a MakeTable query. Note that you can have Make Table queries select
data from one database and write to another.

 

[LarryP]

Oops, never mind. I got it to work using TransferSpreadsheet. Guess I
should have realized that copying an OBJECT is going to create that same kind
of OBJECT in the other database. Duh....