asked on

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

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

omgang

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

VoodooFrog

ASKER

Can you set a report's Recordset when the report is in design view? I will give this a try soon.

omgang

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

omgang

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

VoodooFrog

ASKER

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.

omgang

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

VoodooFrog

ASKER

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.

omgang

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

VoodooFrog

ASKER

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.

omgang

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

VoodooFrog

ASKER

Interesting, how do you create a pass-through query?

ASKER CERTIFIED SOLUTION

omgang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VoodooFrog

ASKER

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

Never realized you could do that.

Thank you!

omgang

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