How to copy queries/tables from one database to another?

[feipezi]

Hi,

I tried to copy... as what the subject says by coding like the following but I got error "2486" and there was no clear desc as why it was not doable.

As I pressed Debug, it points to the doCmd.CopyObject row. Cannot explain why. All in all, it's a simple process trying to avoid keying in the name of each query.

Thanks in advance.


Sub CopyQueries()
Dim dbs As Database
Dim qdf As QueryDef
Dim objAcc As Object

Set objAcc = New Access.Application
objAcc.OpenCurrentDatabase ("C:\jqz\kmk\MM\CB\LTC_Chargeback.mdb")
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs

objAcc.DoCmd.CopyObject "C:\jqz\kmk\MM\CB\RESULTS-FEDERAL.mdb", , acQuery, qdf
objAcc.CloseCurrentDatabase

Set objAcc = Nothing
Next
End Sub

 

[softhemc]

I haven't used Access for a long time, but I think there is an Export/Import Wizard. Additionally, I think that Drag & Drop might be possible between the two databases.

 

[lameid]

Lookup docmd.transferdatabase

 

[feipezi]

Hi lameid,

I tried your way but I got error code '91', on For Each qdf...

Thanks.


Sub tnt()
Dim objAcc As Object
Dim dbs As Database
Dim qdf As QueryDef

Set objAcc = New Access.Application
For Each qdf In dbs.QueryDefs
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\jqz\kmk\MM\CB\LTC_Chargeback.mdb", acQuery, qdf, qdf
Next
End Sub

 

[MajP]

Any reason to do this in code? It is extremely simple to import any or all objects from another database.

 

[lameid]

You are not setting your database object dbs to anything...

Even so I'm not sure about setting an object to a querydef and trying to transfer it... you would at least need qdf.name instead of qdf to pass the names.

I'd loop the allqueries collection as below to get all the names as I'd expect fewer issues with this case.

Sub tnt()

Dim aco As AccessObject

For Each aco In Application.CodeData.AllQueries
[indent]DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\jqz\kmk\MM\CB\LTC_Chargeback.mdb", acQuery, aco.name, aco.name[/indent]
Next Aco
End sub