Converting Access query to Pass-Through Query

[PaulSpell]

Can anyone help? I have a query that queries against 11 linked views against an SQL Server backend.

The query is running dog slow, so I want to convert it to a pass-through query so that the processing is done server side rather than Access having to drag thousands of records across the network, but don't know where to start.

The problem is that I need to convert the Access SQL to a version of SQL that SQL Server understands.

Is there a tool I can use that does this automatically (keeping my fingers crossed here)? If not then does anyone know where I can find out how to do this?

 

[Meltdown]

Is this an ADP project you have Paul?, do you have access to the Enterprise Manager in Sql Server?... show us your Access query that links the views, please.

 

[PaulSpell]

Sorry for the delay getting back to you, I've been off for Christmas.

In answer to your questions:

1. No this is not an ADP project. This is a database that has been set up for reporting against data held in our data warehouse. We plan a move to BusinessObjects but in the meantime need to use Access. Currently I am using ODBC to link to the relevant tables and run my queries over the linked tables, but this is running really slow. So I want to use pass thorugh quesries to try and speed things up.

2. No I don't have access to the Enterprise Manager in Sql Server.

3. Here is the query as it stands:

SELECT dbo_vReturnVersion.ReturnVersionDesc, dbo_vSyndicate.CalYearID AS CalYr, Right([ReturnCalQuarterID],1) AS Qtr, dbo_vReturnHeader.SyndicateNumber, IIf([ReportingCalYearID]=-2,Null,[ReportingCalYearID]) AS YOA, dbo_vReturnBaseElementMappingUIDescription.FormLabel, dbo_vReturnBaseElementMappingUIDescription.YLabel AS RowDesc, dbo_vReturnBaseElementMappingUIDescription.Ref, dbo_vReturnCurrency.CurrencyDesc, CDbl(nz(IIf(IsNull([valueint]),[valuedecimal],[valueint]),0)) AS [Value], dbo_vReturnBaseElementMappingUIDescription.ReturnTypeID, dbo_vReturnVersion.ReturnTypeID, dbo_vReturnStatus.ReturnStatusID, dbo_vReturnStatus.ReturnStatusDesc
FROM (((((((dbo_vReturn LEFT JOIN dbo_vReturnBaseElementMapping ON dbo_vReturn.ReturnBaseElementMappingID = dbo_vReturnBaseElementMapping.ReturnBaseElementMappingID) LEFT JOIN dbo_vReturnHeader ON dbo_vReturn.fReturnHeaderID = dbo_vReturnHeader.fReturnHeaderID) LEFT JOIN dbo_vReturnPeriod ON dbo_vReturnHeader.ReturnPeriodID = dbo_vReturnPeriod.ReturnPeriodID) LEFT JOIN dbo_vSyndicate ON dbo_vReturnHeader.SyndicateSCDYearID = dbo_vSyndicate.SyndicateSCDYearID) LEFT JOIN dbo_vReturnVersion ON dbo_vReturnPeriod.ReturnVersionID = dbo_vReturnVersion.ReturnVersionID) LEFT JOIN dbo_vReturnStatus ON dbo_vReturnHeader.ReturnStatusID = dbo_vReturnStatus.ReturnStatusID) LEFT JOIN dbo_vReturnCurrency ON dbo_vReturn.ReturnCurrencyID = dbo_vReturnCurrency.ReturnCurrencyID) LEFT JOIN dbo_vReturnBaseElementMappingUIDescription ON dbo_vReturnBaseElementMapping.ReturnBaseElementMappingID = dbo_vReturnBaseElementMappingUIDescription.ReturnBaseElementMappingID
WHERE (((dbo_vReturnBaseElementMappingUIDescription.ReturnTypeID)=3) AND ((dbo_vReturnVersion.ReturnTypeID)=3) AND ((dbo_vReturnStatus.ReturnStatusID)=6) AND ((Trim(Left([formlabel],3)))=100))
ORDER BY dbo_vReturnVersion.ReturnVersionDesc, dbo_vSyndicate.CalYearID, Right([ReturnCalQuarterID],1), dbo_vReturnHeader.SyndicateNumber, IIf([ReportingCalYearID]=-2,Null,[ReportingCalYearID]);

Any advice?