By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Open a report from a different Access Database

beacon
100+
P: 579
Hi everybody,

I'm not sure if this is even possible, but I thought I'd toss it out there since my search results haven't pulled up anything and I'm curious whether or not it can actually be done.

I have a database that I'm trying to keep as lean as possible, but I need to link to an Oracle Database that has tons of records in order to create a report for a stakeholder. I feel like this will inevitably slow down my current Access Database.

So, to prevent this from happening, I thought I would copy my current database, link to the Oracle table, create the report, then execute it from my current database. I have a form in my current database that accepts parameters and I'd like to be able to use this same form in the current database and just have the report execute independently from the copy database without the copy database visibly opening for the user.

Thanks,
beacon
Jan 31 '10 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
It's possible by using a so-called "Pass Through" query.
As you can read in the help file, this query needs to be in Oracle SQL and will be sent as a string to Oracle and only the results are returned.

This query can be controlled from code by stringing the needed SQL and parameters.
Expand|Select|Wrap|Line Numbers
  1. Dim qd as Querydef
  2.  
  3. set qd = currentdb.querydefs("qryPassThroughOracle")
  4. qd.SQL = "<your SQL string in Oracle SQL>"
  5.  
  6. ' Now the query can be used by a report or otherwise.
  7.  
Getting the idea ?

Nic;o)
Jan 31 '10 #2

ADezii
Expert 5K+
P: 8,597
I'm not sure of the wisdom of Opening a Report in an External Database, which in turn Links to an Oracle DB, but here is the general idea:
Expand|Select|Wrap|Line Numbers
  1. 'Modular Level Declaration
  2. Dim appAccess As Access.Application
Expand|Select|Wrap|Line Numbers
  1. Dim appAccess As Access.Application
  2. '*********************************************************************
  3. 'Initialize string to Database Path.
  4. Const conPATH_TO_EXTERNAL_DB As String = "C:\YaDaYaDa\SomeDB.mdb"
  5.  
  6. Const con_REPORT_NAME As String = "Report Name"
  7. '*********************************************************************
  8.  
  9. Set appAccess = CreateObject("Access.Application")
  10.  
  11. appAccess.Visible = True
  12.  
  13. 'Open database in Current Microsoft Access window.
  14. appAccess.OpenCurrentDatabase conPATH_TO_EXTERNAL_DB
  15.  
  16. 'Open Report
  17. appAccess.DoCmd.OpenReport con_REPORT_NAME, acViewPreview
Jan 31 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
@beacon
This won't work. If the report were to run from within the copy database, then it would need to be visible for you to see the report itself. An exception to this is if you wanted it as hard-copy (on paper) directly with no need to see it on screen.

With pass-thrus, it's always a very good idea to consider the properties carefully before use. Various of them are specific to pass-thrus so you should make yourself aware of them when you first start to use these queries.

Good luck & I hope it all works out for you :)
Feb 1 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.