Using a RECORDSET in a Report

[ds2728]

Hello,

I am converting a standard access application that is setup with the data and application split. I have problems with the speed of the application due to the slower data link between buildings.

I have been successful in converting the data to MS sql server 2000, with all data access using direct sql calls to the sql server. I have setup all forms, listboxes, etc to use recordests, example: set me!list2.recordset = rst

This works great. My Question is: How do I set a report to use a recordset instead of accessing a local table, linked table, or query? I want to open a recordset then set the data source to use that recordset.

I currently use: Reports!RPT_name.RecordSource = me.openargs

where me.openargs is the query string I pass to the report.

Thanks in advance.

Dave

 

[dhookom]

I am not aware that a report can be based on a recordset. I would use a pass-through query if performance was an issue. You might need to use a little code to change the SQL property of the pass-through.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeAtWork]

Sadly, I have found that you cannot set the record source of a report directly to a recordset like you can with forms.

My solution has been to create a local table on the front-end, which I clear before the report is run. I open a recordset using a stored procedure and use it to populate the local table. I then run the report, which is bound to the local table.

 

[genomon]

You can set the RecordSource property of the report to any valid SQL statement you like, without opening a recordset.
This is exactly the same as using a stored query, which in turn returns a recordset.

It's always darkest before dawn. So if you're going to steal your
neighbor's newspaper, that's the time to do it.

 

[ds2728]

Thanks for the input, I was actually doing what Joeatwork suggested, which worked good. I ended up building a function that builds a pass through query using the querydef statement, which I add all information for qry including the sql server connection string. Then I actually pointed all my reports to the same query. It really works seamless.

Thanks for the input.

Dave

 

[dhookom]

You shouldn't need to build a new pass-through query every time. I just change the SQL property of an existing P-T like:

CurrentDb.QueryDefs("qsptMyPT").SQL = "EXEC ..."

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ds2728]

I pass the sql string to the function that's required for the desire report, then build the qry. Then when I open my report the data source is already set to use
the query: "rpt_qry"

here's my code:

Function BuildPassthruQry(strSql As String) As Boolean

Dim qfd As QueryDef
Dim db As Database
Set db = CurrentDb

DoCmd.DeleteObject acQuery, "rpt_qry"

Set qfd = db.CreateQueryDef("rpt_qry")
qfd.sql = strSql
qfd.Connect = "ODBC;DRIVER=SQLServer;SERVER=xxxx-TRUSTSQL;DATABASE=timedemo;UID=Username;PWD=Password"
qfd.Close

End Function

If you are saying there's an easier way, I guess I don't understand....

 

[dhookom]

Why delete and the create? Try the following code. I have similar code but pass in the query name also so the code can be used to change the SQL of any querydef in my database.

Function BuildPassthruQry(strSql As String) As Boolean
    Dim qfd As QueryDef
    Dim db As Database
    Set db = CurrentDb
    [s]'DoCmd.DeleteObject acQuery, "rpt_qry"[/s]
    [s]'Set qfd = db.CreateQueryDef("rpt_qry")[/s]
    Set qfd = db.QueryDefs("rpt_qry")
    qfd.sql = strSql
    [s]'qfd.Connect = "ODBC;DRIVER=SQLServer;SERVER=xxxx-TRUSTSQL;DATABASE=timedemo;UID=Username;PWD=Password"[/s]
    'qfd.Close
    Set qfd = Nothing
    Set db = Nothing
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ds2728]

Thanks for the clarification.

I have another post concerning this same application running on access 2000 where it errors out.

Post is labeled: Connecting to SQL server backend

Any help on this would also be appreciated......