Report Recordset problem

[Timax]

Hi all, please share your knowloedge about my item if someone knows.
Have a question. I migrated to MS SQL BE and no I am trying to move all my reports queries to SQL Server as well and need to know how to assign recordset of the report that way that it will run on directly on SQL Server because they are running very slow. I created code:
Dim rs As ADODB.Recordset
Set cnn = CurrentProject.AccessConnection Set rs = New ADODB.Recordset
rs.Open MainCode, cnn

'Set the report's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cnn = Nothing

MainCode here is a SQL statement that I cam assembling in VB. This Me.Recordset doesn't work and gives me error "This feature only available in ADP". How can I run report with query directly from SQL Server? Thank you, Tim.

 

[Galaxiom]

Access cannot assign a recordset to a report.

You need to look for the reasons your queries are slow. Was it slow in Access?

Are there joins to local tables or Access user defined functions in the queries? These would make it very slow.

Make sure appropriate indexes are in place on the server tables.

The code you showed doesn't really achieve anything because it is still running on linked tables.

You could use a PassThrough query but Access passes queries to the server anyway if it can so that on its own won't help.

Getting the top speed requires some clever construction. The server needs to run a Stored Procedure so that it is precompiled. But the stored procedure can only return a recordset to Access so you can't apply it to your report. And Access can't link to a Stored Procedure.

You can use a PassThrough query to get the Stored Procedure but it can be messy with the parameters.

Another trick is to create a Table Valued function that calls the Stored Procedure. Then call that function via a PassThrough from Access. This will give you the data for the report.

If you need to pass parameters then the SQL of the PassThrough query can be edited before it is run.

 

[Timax]

Thank you Galaxiom! I have all indexes in place and yes, I have Access user defined functions in the queries. Ones I moved to SQL Server, everything became very slow and I had to rewrite all code to retrieve records to recordset of controls and all works perfectly now. Only reports are the problem because I can not set rs on them... Should I try to do it with Querydefs you think?

 

[Galaxiom]

The user functions can kill the speed. If the functions are used to select the whole table might need to be passed over.

Recreate the functions on the server and use PassThrough queries.

 

[Rx_]

Galaxiom is absolutely spot-on. Any functions being called on the Access Query side slow down .... everything.
My reports use Excel instead of the Access Report. But, the cause and effect are the same.
When Linked Tables in an Access Query, ODBC will take the pure SQL statements and send them over to SQL Server for efficiency.
The moment a VBA or non-SQL function is used (e.g IIF or a custom function), all of that efficiency goes away.

The Pass-through queries are an excellent way to increase efficiency as Galaxiom suggest.

My approach was to re-write the functions in T-SQL. Then create a view, joining all tables / views on the SQL Server side and applying the T-SQL functions in the SQL Server view.
From there, use the view as a Linked Table in Access. This will work for reports or read-only uses.

In my case, the result of moving formulas to the SQL Server was many times faster. Most of my functions regard regulatory conditions with multiple Case and If statements that also perform look-up values from non-linked tables. One view has over a dozen T-SQL functions each applied to a column on a view. The old VBA function code from Access in a query for 20,000 records could take 30 minutes. The Code from SQL Server (as a view with T-SQL functions) is now only a few seconds.

This information is shared to encourage you. It sounds as if your formula or functions in the query may be complex too. This gives you an opportunity to learn something new.

 

[Timax]

I solved my problem. HEre is what i did:
create the SQL statement in code and use DAO to update
the SQL property of your p-t query:

CurrentDb.QueryDefs("qsptYourQueryName").SQL = strSQL

Simple and works fast like crazy! thank you