Is there a way you can open up a report that is stored in one database from another?
I have three databases.
DB1: Holds data and reports
DB2: Is a transfer database which takes the data in DB1 and makes a back up of that database, transfers it to a master DB (DB3) and clears out all tables in DB1.
DB3: Holds all appended data
What im trying to do is when I transfer from DB1 to DB3 by using DB2 I would like for DB2 to call DB1's transfer report to indicate how many records by table were transfered to DB3. I have no tables or reports in DB2, just a transfer module that is executed on button click. I have everything working right execpt I'm not sure how I can open up the report in DB1 after I transfer in DB2. Any help would be great.
This is what I got so far:
Code:Public Function Harvester() Dim dbsTo, dbsFrom As Database Dim rsHarvest, rsTo, rsFrom As DAO.Recordset Dim i, RecCount As Integer Dim PathName, DbName, TableName, Action As String Dim wrkDefault As Workspace On Error GoTo HarvestErrorHandler DoCmd.Hourglass (True) '----------------------------------------- 'First, make a backup copy of the database '----------------------------------------- Action = "Backup" 'Used to show current step if error occurs 'PathName = "D:\Backup\QC Backup" PathName = Application.CurrentProject.Path + "\Backup" If Len(Dir(PathName, vbDirectory)) < 1 Then MkDir (PathName) End If DbName = "\" + Format(Now) + ".MDB" DbName = Replace(DbName, "/", "_") DbName = Replace(DbName, ":", "_") DbName = PathName + DbName Set wrkDefault = DBEngine.Workspaces(0) Set dbsTo = wrkDefault.CreateDatabase(DbName, dbLangGeneral) 'Set dbsFrom = CurrentDb Set dbsFrom = DBEngine.OpenDatabase("C:\FBCB2_LUT.MDB") Set rsHarvest = dbsFrom.OpenRecordset("HARVEST") Do While Not rsHarvest.EOF() TableName = rsHarvest.Fields("TABLE") DoCmd.CopyObject DbName, TableName, acTable, TableName rsHarvest.MoveNext Loop dbsTo.Close '------------------------------------ 'Second, copy tables to harvest drive '------------------------------------ Action = "Harvest" Set dbsTo = DBEngine.OpenDatabase("C:\Master.MDB") rsHarvest.MoveFirst Do While Not rsHarvest.EOF() TableName = rsHarvest.Fields("TABLE") Set rsFrom = dbsFrom.OpenRecordset(TableName) Set rsTo = dbsTo.OpenRecordset(TableName) RecCount = 0 Do While Not rsFrom.EOF() RecCount = RecCount + 1 rsTo.AddNew For i = 0 To rsFrom.Fields.Count - 1 If rsTo.Fields(i).NAME <> rsFrom.Fields(i).NAME Then MsgBox "Error: " + TableName + " Fields not the same" GoTo Harvester_Exit End If rsTo.Fields(i) = rsFrom.Fields(i) Next rsTo.Update rsFrom.MoveNext Loop rsTo.Close rsFrom.Close rsHarvest.Edit rsHarvest.Fields("RecCount") = RecCount rsHarvest.Update rsHarvest.MoveNext Loop '----------------------------------------------- 'Last, If no error yet then clear out the tables '----------------------------------------------- Action = "Clear" rsHarvest.MoveFirst Do While Not rsHarvest.EOF() TableName = rsHarvest.Fields("TABLE") Set rsFrom = dbsFrom.OpenRecordset(TableName) Do While Not rsFrom.EOF() rsFrom.MoveNext Loop rsFrom.Close rsHarvest.MoveNext Loop rsHarvest.Close dbsFrom.Close MsgBox "Harvest complete" Action = "Report" 'This is where i'm stuck. Here is where I want to open up the report that is in DB1. The way I have it here it opens up the report if I had one in DB2. How can I go about calling the report in DB1 at this step? DoCmd.OpenReport ("Rpt_Transfer_Summary") Harvester_Exit: DoCmd.Hourglass (False) Exit Function HarvestErrorHandler: MsgBox "Error: " + Err.Description + _ CStr(Err.Number) + " Table: " + TableName + _ " Action: " + Action GoTo Harvester_Exit End Functio


Reply With Quote