Open a report from a different Access Database

[beacon]

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

[nico5038]

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.

Dim qd as Querydef

set qd = currentdb.querydefs("qryPassThroughOracle")
qd.SQL = "<your SQL string in Oracle SQL>"

' Now the query can be used by a report or otherwise.

Getting the idea ?

Nic;o)

[ADezii]

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:

'Modular Level Declaration
Dim appAccess As Access.Application

Dim appAccess As Access.Application
'*********************************************************************
'Initialize string to Database Path.
Const conPATH_TO_EXTERNAL_DB As String = "C:\YaDaYaDa\SomeDB.mdb"

Const con_REPORT_NAME As String = "Report Name"
'*********************************************************************

Set appAccess = CreateObject("Access.Application")

appAccess.Visible = True
    
'Open database in Current Microsoft Access window.
appAccess.OpenCurrentDatabase conPATH_TO_EXTERNAL_DB

'Open Report
appAccess.DoCmd.OpenReport con_REPORT_NAME, acViewPreview

[NeoPa]

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.

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 :)