We help IT Professionals succeed at work.

MS Access 2007: Attach a reports recordsource to a query in another database.

VoodooFrog
VoodooFrog asked
on
I have done this several times with forms, however this is the first time I am attempting the implementation with a report.  I am used to being able to dynamically setting the recordset when I am trying to connect to a data source external to the database.

When I attempt set the recordset of a report i get the following:
    Run-time error '32585':
    This feature is only available in an ADP.

Is my only option going to be to insert the recordset into a temp table?

This is the typical code I put into the OnOpen event:
dim db as Database
dim rs as DAO.Recordset2

set db = OpenDatabase("\\Server\Path\LegacyData.accdb")

set rs = db.openrecordset(SQL, dbopendynaset, _
    dbseechanges, dboptimistic)

set me.recordset = rs

set rs = nothing
set db = nothing

Open in new window

Comment
Watch Question

omgangIT Manager
CERTIFIED EXPERT

Commented:
My first thought is to try and set the report RecordSource from outside the report itself, e.g. from a sub/function that calls/opens the report.  I do this to specify a specific printer for a report or to rename a report prior to opening.  I'm thinking you'll need to open the report object in design mode, set its RecordSource, save it, and then open it in Print Preview or Normal as desired.  This won't work in an mde/accde as you won't be able to manipulate the reports design.

OM Gang

Author

Commented:
Can you set a report's Recordset when the report is in design view?  I will give this a try soon.
omgangIT Manager
CERTIFIED EXPERT

Commented:
Works slick.
OM Gang


Public Function SetReportSource()
On Error GoTo Err_SetReportSource

    Dim rpt As Report
    Dim strReportName As String, strNewSource As String
   
    strReportName = "rptCategories"
   
        'table/query name we want to set as the RecordSource for the report
    strNewSource = "tblCategories_Copy"
   
    DoCmd.OpenReport strReportName, acViewDesign
    Set rpt = Reports(strReportName)
   
        'output reports current RecordSource
    Debug.Print rpt.RecordSource
        'set reports RecordSource to a copy of the original table
    rpt.RecordSource = strNewSource
        'output reprots new RecordSource
    Debug.Print rpt.RecordSource
        'close the report and save the changes
    DoCmd.Close acReport, strReportName, acSaveYes
   
Exit_SetReportSource:
        'destroy object variables
    Set rpt = Nothing
    Exit Function

Err_SetReportSource:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure SetReportSource of Module Module8"
    Resume Exit_SetReportSource

End Function
omgangIT Manager
CERTIFIED EXPERT

Commented:
VooDooFrog, I'm a little slow.  My sample shows how to change the RecordSource but I see (now) that you're asking how to assign a RecordSet at runtime.  Have you tried moving your procedure to different events?

OM Gang

Author

Commented:
I have tried it in the report's open event.  I have tried it after the report is loaded.  I even on a stretch tried it in design view just a moment ago, which, no you cannot assign a recordset in design view (I didn't think it would work).  The reason I need to do it through a recordset is because it is legacy data that is not offen needed.  If I link my Access front end directly to the ACCDB then the whole front end dramatically slows - especially the design portion.  I have tried troubleshooting that bit, but it seems to be the result of many users connecting to the same database.

Long story short, I need to connect a report's data source to a database not connected to the front end.
omgangIT Manager
CERTIFIED EXPERT

Commented:
Found this http://stackoverflow.com/questions/242504/accesshow-can-i-generate-a-report-of-a-recordset

Says you can instantiate a RecordSet and then refer to it by name in setting the RecordSource for the report.  Give it a shot.
OM Gang

Author

Commented:
Closer, but no go.  rst.name simply returns the select statement.  This does not include the connection or any of the data.  It is no different than setting the Recordsource property to a string containing SQL.
omgangIT Manager
CERTIFIED EXPERT

Commented:
...and that does work slick.  I cleared the reports RecordSource and then resaved it.  Running the function opens the report in preview mode with the desired data.
OM Gang


module 9 code:

Option Compare Database
Option Explicit

    'public recordset object variable
Public gMyRecordset As Recordset



Public Function OpenReportWithRecordset()
On Error GoTo Err_OpenReportWithRecordset

    Dim strTableOrQuery As String, strReportName As String
   
    strTableOrQuery = "tblCategories"
    strReportName = "rptCategories"
   
        'create recordset of specified table/query
    Set gMyRecordset = CurrentDb.OpenRecordset(strTableOrQuery)
   
        'open the report
    DoCmd.OpenReport strReportName, acViewPreview
   
    gMyRecordset.Close

Exit_OpenReportWithRecordset:
        'deestroy object variable
    Set gMyRecordset = Nothing
    Exit Function

Err_OpenReportWithRecordset:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure OpenReportWithRecordset of Module Module9"
    Resume Exit_OpenReportWithRecordset

End Function


report code:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

    Me.RecordSource = gMyRecordset.Name

Exit_Report_Open:
    Exit Sub

Err_Report_Open:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure Report_Open of VBA Document Report_rptCategories"
    Resume Exit_Report_Open

End Sub

Author

Commented:
Yes, it works, but if you try it with a table not linked to your current ACCDB, it fails because the recordset in the code above is unnecessary.  You would get the same result above if you simply set the report recordsource to strTableOrQuery.  

omgangIT Manager
CERTIFIED EXPERT

Commented:
Interesting.  Perhaps your initial assumption about creating a local temp table to store the recordset data is accurate.

Another idea is to create a pass-thorugh query to the other database.  This pass-through query will be local to your app and you will be able to refer to it by name.  What do you think?
OM Gang

Author

Commented:
Interesting, how do you create a pass-through query?
IT Manager
CERTIFIED EXPERT
Commented:
Create a new query but don't set any source tables/queries.  Display the query property sheet and enter the path to the remote db in the Source Database field.  If you're connecting to another Access db you won't need a connection string.  Now you can click to Show Tables and you'll see the tables and queries in the remote db.  Create your query and save it.  Now you can refer to the query by name for your report RecordSource.
OM Gang

Author

Commented:
Wonderful! Perhaps not quite as fast as a prefiltered recordset, but serves the purpose!

Never realized you could do that.

Thank you!
omgangIT Manager
CERTIFIED EXPERT

Commented:
You're welcome.  I use pass-though queries to SQL and Oracle db tables.  A pass-through query sends the SQL statement to be executed by the remote db engine and often times provides a performance boost over a local Access query using linked tables.  We found another use for them today.
OM Gang