How to open a report on another database from a form? 1

[imendi]

Hi,

I have database db1 where I have my code and forms, and db2 where I have my reports.
From a form in db1 I would like to open a report in db2.
How could I do that?

Thanks,

imendi

 

[danvlas]

Why would you do that? Import all reports in the database with code and forms and open the reports from there...

Unless you have a special reason to do it your way...


Daniel Vlas
Systems Consultant

 

[imendi]

The reason is that the 5 reports that I have, imply 20 MB, so I don´t want to load these unless it is necessary.
Most of the people will work with no so powerful notebooks, and most of the times (95% I would say), they won´t use these reports. But just in case, I want to have them available.

Imendi

 

[danvlas]

Create a table (BufferTable) in either database. It should have 2 fields: ReportName and FilterCriteria
Link it to the other database
Create a small form in the 'Reports' database and use the Load event (the commented code is for ADO):

Private Sub Load()
Dim rst As DAO.Database
'Dim rst As New ADODB.Recordset

Set rst = CurrentDB.OpenRecordset("Select * From BufferTable;&quot

'rst.Open "select * from buffertable;", CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic


If rst.EOF And rst.BOF Then
MsgBox "No records"
Quit 'no need to keep it open
Else
DoCmd.OpenReport rst.Fields(0), acViewPreview, , rst.Fields(1)
DoCmd.RunSQL "Delete * From BufferTable"
End If
rst.Close
Set rst = Nothing
End Sub

Set this form to load automatically on startup.

Now, the 'Forms' database-the command button that should open the report:

Sub CommandButton_Click()
DoCmd.RunSQL "Insert Into BufferTable (ReportName, FilterCriteria)" _
& "Values ('" & ReportName & "', '" & Criteria & "')"
Call Shell("msaccess.exe " & Chr(34) & "\\ServerName\ShareName\Reports.mdb" & Chr(34), vbMaximizedFocus)
'Quit
End Sub

This will pass the report name and the filter condition to the 'common table' and the values will be used by the startup form in the reports database to open the correct object...


But...
20 MB for 5 reports??? Do you have objects embedded into them? If yes, you could try to link the pictures, this will make the file really smaller
What about the tables-where are they located?


HTH


Daniel Vlas
Systems Consultant

 

[imendi]

Thanks Danvlas. I´ll try your code now.
Related to the 20 MB I have solved it; I have created a table, where I have three fields: ID, description and and OLE object, which is the image itself. You only have to copy and paste into the field once you open the table.

Then I have created a report, that I use as sub-report into the rest of reports. This sub-report has only on image object, which is the image I have stored on the table.

Result, from 20 MB to 1.9 MB.

Ciao!

imendi