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
Solved

open a report from different DB

Posted on 2014-02-05
10
443 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 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