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

Posted on 2004-11-07
Last Modified: 2008-03-03

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.


Question by:balajisundar
    LVL 33

    Expert Comment

    by:Mike Eghtebas
    DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype], source, destination[, structureonly][, saveloginid]

    LVL 33

    Expert Comment

    by:Mike Eghtebas
    LVL 33

    Accepted Solution

    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.


    Author Comment

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

    LVL 33

    Expert Comment

    by:Mike Eghtebas

    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.

    LVL 3

    Assisted Solution

    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 =)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now