Solved

open a report from different DB

Posted on 2014-02-05
10
446 Views
Last Modified: 2014-02-06
Hi all,
I would like a button on a ADP form should open a report saved in a access MDB file (they happen to be linked to the same DB back end).
please let me know how to accomplish that,
Thanks
0
Comment
Question by:bfuchs
  • 5
  • 3
  • 2
10 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 333 total points
ID: 39837178
I would think that you should be able to do the following:
1. instantiate a Access.Application object
2. Open the mdb database
3. Invoke Docmd.OpenReport
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39837220
hi thanks for replying,
do you have a sample of code that does the above?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 39837244
Here's an example:

Dim acc As New Access.Application
acc.OpenCurrentDatabase("Path to your remote database")

acc.DoCmd.OpenReport "YourReportName"
acc.Visible = True

This will open a second instance of Access, and then show your report in that instance of Access.

Or you could import the report into your database, link the needed tables, and then show the report directly. This is often much simpler, and involves far less issues.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 333 total points
ID: 39837316
I usually use late binding.  This version of Scott's code uses late binding.
Dim acc As Object
set acc = createobject("Access.Application")
acc.OpenCurrentDatabase("Path to your remote database")

acc.DoCmd.OpenReport "YourReportName"
acc.Visible = True

Open in new window

================
@bfuchs
Do you need to preview the report or just print the report?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39837322
@Scott,
I am getting an error message that its open exclusively by another user (and that is expected).
when i try acc.OpenCurrentDatabase("DBPath",0)
i get an error expected:=
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39837336
@aikimark
probably need it for both.

also, didn't get what is the difference between scott's code and the one you posted?
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 39837371
actually tested again and works, great job!
Thank you.
0
 
LVL 84
ID: 39837401
also, didn't get what is the difference between scott's code and the one you posted?
aikimark uses what is known as "Late Binding", whereas mine uses "Early Binding". the different is this:

Dim acc As New Access.Application

vs

Dim acc As Object

In mine (the first) I've actually defined the name of the class/object I will use. This allows me to use Intellisense and such, whereas Late Binding does not.

Late Binding is generally easier to deploy, since it does not require you to have a Reference to your library included with your project, and can work against multiple versions.

That said, since you're automating Access (presumably) from within Access, there's no real gain to be made from Late Binding since you obviously have Access running.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39837571
Hi Scott,
Thanks for this detailed info, its a topic that i never had clear understanding of...
Ben
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39839134
Since I usually post code snippets, late binding allows someone to drop it into their project without having to add any references.

Glad this worked for you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question