Solved

open a report from different DB

Posted on 2014-02-05
10
450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 85
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

729 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