How to copy reports and queries from one database to another database through program?


Is there any method (programatically) to

1. copy reports and queries from first database to second database from a third database? The code needs to be present in the third database.

2. Copy reports and queries from a different database to the current database?

I tried using docmd.copyobject but I could only transfer the objects FROM the current database.

Can anyone please help me? It is very urgent pls.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype], source, destination[, structureonly][, saveloginid]

Mike EghtebasDatabase and Application DeveloperCommented:
Mike EghtebasDatabase and Application DeveloperCommented:
Please note:

SourceObjectName  Optional Variant. A string expression that's the valid name of an object of the type selected by the sourceobjecttype argument. If you run Visual Basic code containing the CopyObject method in a library database, Microsoft Access **** looks for the object with this name first in the library database, then in the current database. ****

Also read remarks section, above link.  I think it talks about a way to copy from another databas as well as current (default) database.  I will read abit more.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

balajisundarAuthor Commented:
Thanks Mike. But I am not sure how to include a library database. Is it something we select in tools->references?

Mike EghtebasDatabase and Application DeveloperCommented:

I have no idea myself.  Maybe you could post another question to ask that.  You could request for this question to be closed and refunded if you whish.

Right... I realise this is a VERY VERY old question but, hoping this will help immensely =)

Create a module, with the following code.  This will need to be in the THIRD database:


Option Compare Database

Private appAccess As Access.Application
Private sSourceDBName As String
Private sDestDBName As String

Sub AnythingTransfer_Initialise(Optional sSourceDB, Optional sDestDB)
    If IsEmpty(sSourceDB) Then
        Set appAccess = Application
        Set appAccess = CreateObject("Access.Application")
        appAccess.OpenCurrentDatabase sSourceDB
    End If
    sSourceDBName = appAccess.CurrentDb.Name
    If IsEmpty(sDestDB) Then
        sDestDBName = appAccess.CurrentDb.Name
        sDestDBName = sDestDB
    End If
End Sub

Sub AnythingTransfer_Execute(lObjectType As AcObjectType, sObjectName As String, Optional sDestName As String)
    appAccess.DoCmd.CopyObject sDestDBName, sDestName, lObjectType, sObjectName
End Sub

Sub AnythingTransfer_CleanUp()
    If appAccess.CurrentDb.Name <> CurrentDb.Name Then
        appAccess.Quit acQuitSaveNone
    End If
    Set appAccess = Nothing
End Sub


This will give you three functions for transferring objects:

AnythingTransfer_Initialise ([sSourceDB], [sDestDB])

This will initialise the transfer of data, opening databases where necessary.  I have done this as, with Access 2002 you're prompted with far more security warnings than with other versions of access.  This will mean you only see the warning once, as the database itself is only opened here.

If sSourceDB is ommitted, the current database is assumed.  If sDestDB is ommitted, then this will be assumed to be the same as the source database (even if it's the current one)

AnythingTransfer_Execute(lObjectType, sObjectName, [sDestName])

This is the heart & soul of the routine.  This will use the external database (with the newly created Access.Application object) to transfer the object with DoCmd.CopyObject.  The only drawback here is that if your destination database is the current database, you WILL NEED to have the database open read-only in shared mode.  This is because DoCmd.CopyObject requires to be able to exclusively modify the database.

If sDestName is ommitted, the destination name will be the same as sObjectName


This will close the extra database (if opened) and free up memory used by the Access.Application object.

I know your problem WAS urgent and, if you've not already solved it, then I really do hope this helps.

Just trying to solve some of the older problems and get some questions closed down =)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.