Print a report from another database

[Limp Arbor]

I have an database (A) that allows users to lookup information and
print reports. In a different database (B) I have a historical
report.

The report in database B has a query behind it that is based on a
value from a combo box on a form. The relevant portion of the query
is:
WHERE (((HEADER.doedista)=[Forms]![Main]![districtpicked]))

Is there a way I can open the report in database B from database A by
passing the value for [districtpicked]?

If not, I could make a copy of the report in B and have it based on a
different query but I would still need to either pass the value to the
query or change the query by updating the SQL and changing this line:
WHERE (((HEADER.doedista)="MyValue")).

My objective is not to copy the report, query and underlying tables to
A even though that would probably take less time than the above
options. The data for the report in B is static and does not change.

Any advice is would be appreciated

 

[aaron.kempf]

if you kept your data on SQL Server, it would be easy because all
databases automagically talk to each other in SQL Land.

-Aaron

 

[Limp Arbor]

if you kept your data on SQL Server, it would be easy because all
databases automagically talk to each other in SQL Land.

-Aaron

Sadly I am not part of our IS department so I don't get the double
secret password to the SQL box.

 

[fredg]

I have an database (A) that allows users to lookup information and
print reports. In a different database (B) I have a historical
report.

The report in database B has a query behind it that is based on a
value from a combo box on a form. The relevant portion of the query
is:
WHERE (((HEADER.doedista)=[Forms]![Main]![districtpicked]))

Is there a way I can open the report in database B from database A by
passing the value for [districtpicked]?

If not, I could make a copy of the report in B and have it based on a
different query but I would still need to either pass the value to the
query or change the query by updating the SQL and changing this line:
WHERE (((HEADER.doedista)="MyValue")).

My objective is not to copy the report, query and underlying tables to
A even though that would probably take less time than the above
options. The data for the report in B is static and does not change.

Any advice is would be appreciated

You can adapt this to your own needs. Change the form and control
names if needed.

First remove the criteria from the Database B Report query. It should
display all records.

Then, place this procedure into a module in Database A.
The below assumes [districtpicked] is a Text datatype field that
contains the criteria on the Report contained in Database B, while
[TheDistrict] is the control that contains the value displayed on the
record in FormA in Database A.
FormA must be open when this code is run.


Public Sub SetObjects()
' Run a report located in a different database
Dim appAccess As Access.Application

' Adapted from the OpenCurrentDatabase methiod in access Help
' Open a report in another database
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase "C:\MyFolderName\DatabaseB.mdb"
' Open the Database B Report in Preview
appAccess.DoCmd.OpenReport "ReportName", acViewPreview, ,
"districtpicked] = '" & Forms!FormA![TheDistrict] & "'"

appAccess.Quit
Set appAccess = Nothing
End Sub

Note1. If [districtpicked] is a Number datatype, use:
"[districtpicked] = " & Forms!FormA![TheDistrict]

Note2: Use acViewNormal instead of acViewPreview to print the report
without preview .

 

[aaron.kempf]

so install SQL on your desktop!

SQL Server is an optional component that is included on the Office
Professional disk.
If you can't download it, then bring it in on a thumbdrive.

ADP development is _MUCH_ easier than dealing with 12 layers of jet

-Aaron

 

[BruceM]

Aaron "recommends" SQL Server as the solution to every problem. No doubt
you have noticed his other mission is to trash Access/Jet. His comments
should at best be regarded skeptically.

if you kept your data on SQL Server, it would be easy because all
databases automagically talk to each other in SQL Land.

-Aaron

Sadly I am not part of our IS department so I don't get the double
secret password to the SQL box.

 

[aaron.kempf]

Jet ****ing sucks.

I've seen more Jet corruption that most of the people in this
newsgroup--
Have you ever gotten called out of bed at 3am because Jet sucks and it
has locking problems-- with a single user app--

I have.

Have you ever upsized to SQL Server?

Because you don't know the first thing about SQL Server-- who in the
hell do you think that you are-- running around, telling everyone that
I'm wrong--
just because my databases are more successful than yours-- and they
grow and they can add users without having to re-engineer everything

Does that make me wrong?

No-- it makes me right-- and you're just a little cry-baby Script kid
that thinks that a couple of vba macros-- somehow makes him into a db
architect.
**** OFF BRUCE.

I will recommend SQL Server until they day I die.
Meanwhile-- they're going to pull a foxpro on you dipshits-- oh yah--
they already have!!

-Aaron

 

[Tony Toews [MVP]]

Meanwhile-- they're going to pull a foxpro on you dipshits-- oh yah--
they already have!!

The Access product group is hard at work on the next version of Access. It isn't
going to be cancelled like FoxPro any time soon.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/