Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71

    Question Unanswered: Automating Access - Exporting Reports to other Access Databases

    Im trying to maintain a large number of access databases (50+) which each contain the same set of reports. I want to update the look of all these reports (there are also about 50 reports in each database) and export them to each of the databases.

    I have saved all the new format reports into a separate database and I think it might be quicker to write a module to export each report to all of the databases in a given folder, than right click on each report each time and export it to one database at a time.

    Ps. I know that I can import the reports from each database and that allows me to select more than one at a time, but because I am overwriting existing reports with the same name it is calling the imported ones rtpName1" and not overwriting them. This is no good as it will also take ages to go through and rename them.

    Does anyone have any suggestions as how to go about writing a module to do this? Im fairly new to access, but not so new to VB and VBA. Thanks.

  2. #2
    Join Date
    Apr 2002
    Posts
    139
    Nice question, Emma!
    Something like engineering your own replication engine.
    I spent an hour on it and guess what: It works!

    The module below will replace a given report from your local in a remote DB. Even if the remote report is opened! How about that.

    Hope you will find it usefull. It will need some tuning for your needs, but since you are familiair with VBA, that should work.

    hth

    Sub ReplaceYourReportInOtherDB()

    Dim wrkStandaard As Workspace
    Dim ThisDB As Database
    Dim ctr As Container, doc As Document
    Dim RemoteDb As String

    'Set RemoteDB
    RemoteDb = "f:\clients.mdb"

    'Create Workspace and set reference to ThisDB:
    Set wrkStandaard = DBEngine.Workspaces(0)
    Set ThisDB = CurrentDb

    ' Set reference to the Report Collection in ThisDB:
    Set ctr = ThisDB.Containers!Reports

    ' Cycle through Reports Collection in this DB
    For Each doc In ctr.Documents
    MsgBox doc.Name
    'Copy local report to OtherDB
    'It simply overwrites the report in the other DB, even if that remote DB or the remote report is open!
    DoCmd.TransferDatabase acExport, "Microsoft Access", RemoteDb, acReport, doc.Name, doc.Name, False
    Next doc

    Set ctr = Nothing
    Set ThisDB = Nothing
    Set wrkStandaard = Nothing

    End Sub
    Last edited by marion; 03-15-03 at 14:43.

  3. #3
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    Thanks, I'll give it a go and let you know what happens! Thank you very much,

    Originally posted by marion
    Nice question, Emma!
    Something like engineering your own replication engine.
    I spent an hour on it and guess what: It works!

    The module below will replace a given report from your local in a remote DB. Even if the remote report is opened! How about that.

    Hope you will find it usefull. It will need some tuning for your needs, but since you are familiair with VBA, that should work.

    hth

    Sub ReplaceYourReportInOtherDB()

    Dim wrkStandaard As Workspace
    Dim ThisDB As Database
    Dim ctr As Container, doc As Document
    Dim RemoteDb As String

    'Set RemoteDB
    RemoteDb = "f:\clients.mdb"

    'Create Workspace and set reference to ThisDB:
    Set wrkStandaard = DBEngine.Workspaces(0)
    Set ThisDB = CurrentDb

    ' Set reference to the Report Collection in ThisDB:
    Set ctr = ThisDB.Containers!Reports

    ' Cycle through Reports Collection in this DB
    For Each doc In ctr.Documents
    MsgBox doc.Name
    'Copy local report to OtherDB
    'It simply overwrites the report in the other DB, even if that remote DB or the remote report is open!
    DoCmd.TransferDatabase acExport, "Microsoft Access", RemoteDb, acReport, doc.Name, doc.Name, False
    Next doc

    Set ctr = Nothing
    Set ThisDB = Nothing
    Set wrkStandaard = Nothing

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •