[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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

Hi,

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.

balajisundar

0
balajisundar
Asked:
balajisundar
  • 4
2 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype], source, destination[, structureonly][, saveloginid]

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
0
 
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.

Mike
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

balaji
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
balaji,

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.

Mike
0
 
hairymingaCommented:
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
    Else
        Set appAccess = CreateObject("Access.Application")
        appAccess.OpenCurrentDatabase sSourceDB
    End If
    sSourceDBName = appAccess.CurrentDb.Name
    If IsEmpty(sDestDB) Then
        sDestDBName = appAccess.CurrentDb.Name
    Else
        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

AnythingTransfer_CleanUp

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now