Solved Pass-through queries in SQL server

[Kayleigh]

Hi,
I am working on migrating a large database system onto SQL server. However I confess it is my first time doing this so I'm working trial-and-error to get it functional. I am a little stuck on rewriting some queries. They are currently running on front-end MS Access Jet engine but must be converted to pass-through queries for best results. I have attempted writing such queries in Access and SQL Server Studio but neither attempt was successful. Some queries I had were regarding the correct syntax and also how to find the connection string.
Would appreciate if anyone can offer a few sample queries to model the correct approach. And also any pointers to helpful resources for this - have looked and looked on the web but didn't come across much
Cheers

 

[theDBguy]

Hi. Most Access queries should work in SQL Server. It might be easier if you could post a sample of one of your queries that's giving you problems converting, and we can try to see why.

 

[Isaac]

you can build the connection string by clicking the ellipsis button in the properties pane of the pass through query.

for actually writing queries in t-sql, we also have a sql server forum if you want to post there/as well.

 

[Kayleigh]

Thank you. I have several queries built on each other.
This is one such query:

SELECT qryCashProjection.cfweekno AS cfWeek, Sum(qryCashProjection.cfACE) AS cfSumACE, Sum(qryCashProjection.cfADS) AS cfSumADS, Sum(qryCashProjection.cfTfrBal) AS cfSumTfr, Sum(qryCashProjection.cfMzBal) AS cfSumMz, Sum(qryCashProjection.cfFinanceBal) AS cfSumFinance, Sum(qryCashProjection.cfBalance) AS cfSumTotal
FROM qryCashProjection
GROUP BY qryCashProjection.cfweekno;

And this is another:

SELECT qryAmountDue.fldOrderID, qryAmountDue.fldOCompanyID, qryAmountDue.fldOTotalQuote, qryAmountDue.fldOVatRate, qryAmountDue.fldOVatPercentage, qryAmountDue.fldOAgentCommision, qryAmountDue.fldODepositPercentage, qryAmountDue.fldODepositRcvd, qryAmountDue.fldOFinance, qryAmountDue.fldOFinanceUploaded, qryAmountDue.fldOFinancePaperworkSent, qryAmountDue.fldOAnticipatedPayDate, qryAmountDue.cfVat, qryAmountDue.cfAgent, qryAmountDue.cfGrandTotal, qryAmountDue.cfDepositAmt, qryAmountDue.cfMzDue, qryAmountDue.cfTfrDue, qryAmountDue.cfFinanceDue, qryAmountDue.cfGrandTotalDueIn, qryOrderTotalPaid.cfTotalPaid, qryOrderTotalPaidMz.cfTotalPaidMz, qryOrderTotalPaidFinance.cfTotalPaidFinance, CCur(Nz([cfTotalPaid])-Nz([cftotalPaidmz])) AS cfTotalPaidTfr, IIf(([cfmzdue]+Nz([cfTfrOverPayvat])-(Nz([cfTotalPaidMz])+[cfTfrOverPay]))<0,0,([cfmzdue]+Nz([cfTfrOverPayvat])-(Nz([cfTotalPaidMz])+[cfTfrOverPay]))) AS cfMzBal, IIf(([cftfrdue]-([cftotalpaidtfr]+[cfMzoverpay]))<0,0,([cftfrdue]-([cftotalpaidtfr]+[cfMzoverpay]))) AS cfTfrBal, IIf([fldofinance]=True,CCur(Nz([cfFinanceDue])-Nz([cfTotalPaid])),0) AS cfFinanceBal, ([cfGrandTotalDueIn]+Nz([cfTfrOverPayVat]))-Nz([cfTotalPaid]) AS cfBalance, IIf((([cfmzdue]-Nz([cfTotalPaidMz]))*-1)<0,0,([cfmzdue]-Nz([cfTotalPaidMz]))*-1) AS cfMzOverPay, IIf([fldofinance]=False,CCur(IIf((([cftfrdue]-Nz([cfTotalPaidtfr]))*-1)<0,0,([cftfrdue]-Nz([cfTotalPaidtfr]))*-1)),0) AS cfTfrOverPay, CCur(Nz([cftfroverpay])*Nz([fldOVatRate])/100) AS cfTfrOverPayVat, IIf([fldOCompanyID]=1,[cftfrbal],0) AS cfACE, IIf([fldOCompanyID]=2,[cftfrbal],0) AS cfADS, IIf(DatePart("ww",[fldOAnticipatedPayDate])=53,1,DatePart("ww",[fldOAnticipatedPayDate])) AS cfweekno
FROM qryOrderTotalPaidFinance RIGHT JOIN ((qryAmountDue LEFT JOIN qryOrderTotalPaid ON qryAmountDue.fldOrderID = qryOrderTotalPaid.fldTOrderID) LEFT JOIN qryOrderTotalPaidMz ON qryAmountDue.fldOrderID = qryOrderTotalPaidMz.fldTOrderID) ON qryOrderTotalPaidFinance.fldTOrderID = qryAmountDue.fldOrderID
WHERE (((qryAmountDue.fldOTotalQuote) Is Not Null));

If I have a method to follow, I should be fine with working through the rest...

 

[theDBguy]

Thank you. I have several queries built on each other.
This is one such query:

SELECT qryCashProjection.cfweekno AS cfWeek, Sum(qryCashProjection.cfACE) AS cfSumACE, Sum(qryCashProjection.cfADS) AS cfSumADS, Sum(qryCashProjection.cfTfrBal) AS cfSumTfr, Sum(qryCashProjection.cfMzBal) AS cfSumMz, Sum(qryCashProjection.cfFinanceBal) AS cfSumFinance, Sum(qryCashProjection.cfBalance) AS cfSumTotal
FROM qryCashProjection
GROUP BY qryCashProjection.cfweekno;

And this is another:

SELECT qryAmountDue.fldOrderID, qryAmountDue.fldOCompanyID, qryAmountDue.fldOTotalQuote, qryAmountDue.fldOVatRate, qryAmountDue.fldOVatPercentage, qryAmountDue.fldOAgentCommision, qryAmountDue.fldODepositPercentage, qryAmountDue.fldODepositRcvd, qryAmountDue.fldOFinance, qryAmountDue.fldOFinanceUploaded, qryAmountDue.fldOFinancePaperworkSent, qryAmountDue.fldOAnticipatedPayDate, qryAmountDue.cfVat, qryAmountDue.cfAgent, qryAmountDue.cfGrandTotal, qryAmountDue.cfDepositAmt, qryAmountDue.cfMzDue, qryAmountDue.cfTfrDue, qryAmountDue.cfFinanceDue, qryAmountDue.cfGrandTotalDueIn, qryOrderTotalPaid.cfTotalPaid, qryOrderTotalPaidMz.cfTotalPaidMz, qryOrderTotalPaidFinance.cfTotalPaidFinance, CCur(Nz([cfTotalPaid])-Nz([cftotalPaidmz])) AS cfTotalPaidTfr, IIf(([cfmzdue]+Nz([cfTfrOverPayvat])-(Nz([cfTotalPaidMz])+[cfTfrOverPay]))<0,0,([cfmzdue]+Nz([cfTfrOverPayvat])-(Nz([cfTotalPaidMz])+[cfTfrOverPay]))) AS cfMzBal, IIf(([cftfrdue]-([cftotalpaidtfr]+[cfMzoverpay]))<0,0,([cftfrdue]-([cftotalpaidtfr]+[cfMzoverpay]))) AS cfTfrBal, IIf([fldofinance]=True,CCur(Nz([cfFinanceDue])-Nz([cfTotalPaid])),0) AS cfFinanceBal, ([cfGrandTotalDueIn]+Nz([cfTfrOverPayVat]))-Nz([cfTotalPaid]) AS cfBalance, IIf((([cfmzdue]-Nz([cfTotalPaidMz]))*-1)<0,0,([cfmzdue]-Nz([cfTotalPaidMz]))*-1) AS cfMzOverPay, IIf([fldofinance]=False,CCur(IIf((([cftfrdue]-Nz([cfTotalPaidtfr]))*-1)<0,0,([cftfrdue]-Nz([cfTotalPaidtfr]))*-1)),0) AS cfTfrOverPay, CCur(Nz([cftfroverpay])*Nz([fldOVatRate])/100) AS cfTfrOverPayVat, IIf([fldOCompanyID]=1,[cftfrbal],0) AS cfACE, IIf([fldOCompanyID]=2,[cftfrbal],0) AS cfADS, IIf(DatePart("ww",[fldOAnticipatedPayDate])=53,1,DatePart("ww",[fldOAnticipatedPayDate])) AS cfweekno
FROM qryOrderTotalPaidFinance RIGHT JOIN ((qryAmountDue LEFT JOIN qryOrderTotalPaid ON qryAmountDue.fldOrderID = qryOrderTotalPaid.fldTOrderID) LEFT JOIN qryOrderTotalPaidMz ON qryAmountDue.fldOrderID = qryOrderTotalPaidMz.fldTOrderID) ON qryOrderTotalPaidFinance.fldTOrderID = qryAmountDue.fldOrderID
WHERE (((qryAmountDue.fldOTotalQuote) Is Not Null));

If I have a method to follow, I should be fine with working through the rest...

Looks like you're querying a query. If so, yes, you can move that query processing to the server by either creating a view or using a PT query. So far, with just doing a quick scan on your SQL statement, the only things I can see you need to update are your use of the Nz() and CCur() functions (there may be others). You will have to replace them with their equivalents in SQL Server, like ISNULL and CONVERT.

 

[Kayleigh]

So do I start with the base query - convert to PT and then move up to convert next query?

 

[theDBguy]

So do I start with the base query - convert to PT and then move up to convert next query?

Not sure I understand your question. Using a PT query means you want the server to do all the work. To do that, the server can only work on what it can reach. So, if you're thinking of converting the base query into a PT query and then also converting the dependent query into a PT query, I don't think that will work.

For instance, if you have a query called Query1 and then create a PT query calling Query1, the SQL Server will not know anything about Query1, since it's only residing in Access.

Is that what you mean?

 

[Kayleigh]

Yes. Thanks for clarifying. So how would you advise I go about converting those queries based on other local queries?

 

[theDBguy]

Yes. Thanks for clarifying. So how would you advise I go about converting those queries based on other local queries?

Like I said earlier, probably convert your base queries into Views.

 

[Minty]

In SSMS it is a lot easier to "nest" queries as you can literally write them in one statement.
So if you post up the initial query, then the summation query we could show you how to write them as one query.

You would then store this as one view.

It is generally frowned upon to base views on views, as you may forget and change something in one view that breaks others that are based on it. It also can lead to poor execution plans on the server.

 

[Pat Hartman]

As others have mentioned, you need to actually determine if the queries need to be converted. Almost all my apps use SQL Server and I rarely have to use pass-through queries. Don't convert them just because some "expert" told you that you have to to improve performance. A poorly performing Access query will almost certainly end up as a worse performing SQL server query.

Did you properly normalize the schema when you did the conversion? Did you make sure that all the necessary indexes were created? Did you make sure that all the relationships were created.

In reality, I've found one situation where I needed to use pass through queries and that was to do bulk updates, especially deletes. Problems caused by multiple joins are usually resolved by creating views. In a couple of cases, I ended up creating stored procedures for very complex reports.

 

[Kayleigh]

Hi I found a useful tool to convert Access queries to SQL on https://www.codeproject.com/Articles/5246516/MS-Access-Queries-to-SQL-Server-Converter
However I am having trouble compiling it. Can anyone guide me how to unzip and run this tool?

 

[Galaxiom]

Access passes queries to the server if they are not too complex. Using pass though often makes no difference. Either use the linked tables or create Views on the server and link them.

Your naming convention is going to look pretty silly in SQL Server which has Columns rather than Fields. SSMS has powerful prompting of names anyway so there is no point to that convention. I don't believe there ever was in Access for that matter.

The far simpler join syntax in SQL Server means it isn't worth trying to translate Access nested joins to SQL server. It doesn't need the horrible nesting required in Access. Only that the table or subquery being joined appears above the join.

SSMS will show the problems with red underlines and tell you what it is when you hover as well as bars down the vertical scrollbar track.

Start your queries by using the Select Top 1000 on the main table's context menu. You can drag in the Column list header from the object browser pane to add the columns. The Find and Replace is powerful especially for converting comma to newline + comma. Edit and format by holding down ALT and using the mouse to select vertically across multiple lines. For example you can add the table names to a vertical list of columns by selecting just in front of the column names and typing the table name simultaneously in all of them.

The real key to writing queries in SQL Server is formatting. It doesn't wreck the formatting like Access does. Spread it out, indent and use whitespace to aid readability. Below is a nonsense query but the formatting is what I'm demonstrating. Each subquery level is indented further.

SELECT
     table1.somecolumn
     ,qSubquery.somealias

FROM
     table1

INNER JOIN
     (
     SELECT
          somecolumn
          ,SUM(acolumn) AS someAlias

     FROM
          tableB

     GROUP BY
          something
         ,somethingelse

     ) AS qSubquery
     ON qSubquery.somecolumn = table1.somecolumn

ORDER BY
     whatever
    ,anothercolumn

I have queries with up to 1500 lines and I don't know how many levels of subqueries in SQL Server. It would be utterly impossible without rigorous formatting.

 

[Isaac]

Sounds like a nice tool if you are looking to move your queries to SQL Server. I currently have a project I'm working on where I installed sql server express and am doing everything in sql server - using Access only to exec procedures (etc) in pass through queries.
It's nice because:
1) I can focus all my attention on sql server programming, rather than try to simultaneously query in both languages, and obviously sql server has many more capabilities - the formatting stuff that G has mentioned, just to begin, not to even start discussing the actual capabilities, and
2) I can take advantages of all that t-sql has to offer, rather than limiting myself to Access and then thinking better of it later and having to convert.
3) I can actually document my query code, extensively... dates changed, why changed, etc. Can't do any of that in Access.

@Krayna
After first, of course, extracting the AccessToSql folder to get it out of the zip file and onto your desktop:
Does it help to look inside AccessToSql\obj\Debug ?

One thing to keep in mind. The way the author of this tool was successful was to simply create CTE's in t-sql, anytime his code discovered nested Access queries. That's a cool idea, I mean......it works, but.....Remember that that may not necessarily/automatically be the "best" approach in t-sql. It may, it may not, I'm just saying he did that not because it was best for you, but because it was the only logic he could think of to make his tool work. Keep that in mind.

 

[Pat Hartman]

Access passes queries to the server if they are not too complex.

Complexity isn't the issue. The issue is whether the query contains any elements that cannot be resolved by SQL Server so queries with VBA or UDF functions cannot be sent as is. Sometimes the ODBC driver can send some of the query and apply the function later, locally when the recordset is returned so something like Select MyFunc(fldx) From YourTable will be sent as Select fldx from YourTable and the recordset will be processed on return using MyFunc(). However, VBA and UDF functions in criteria or elsewhere can result in entire tables being requested from the server.

 

[Galaxiom]

Complexity isn't the issue. The issue is whether the query contains any elements that cannot be resolved by SQL Server so queries with VBA or UDF functions cannot be sent as is. Sometimes the ODBC driver can send some of the query and apply the function later, locally when the recordset is returned so something like Select MyFunc(fldx) From YourTable will be sent as Select fldx from YourTable and the recordset will be processed on return using MyFunc(). However, VBA and UDF functions in criteria or elsewhere can result in entire tables being requested from the server.

Obviously a pass through won't work for resources that are not on the server.

However I think the ODBC may have some limits in its ability to translate Access queries if they are very complex (eg deeply nested joins) even if they don't use local resources. I don't have test results to demonstrate my suspicions though and would always set up views in the server rather than trust it to pass a really complex query.

Like Pat I use parameterised Stored Procedures if the going gets tough and they vastly out-perform anything else for returning a recordset from a complex query.

 

[Pat Hartman]

The only point I want to make is that if you are converting from Jet/ACE to SQL Server et al, there is no need to convert all your queries also or think you have to convert to unbound forms. The vast majority of forms/queries will work fine as they are. That leaves you to concentrate on the ones that might benefit from using views or actually being converted to pass through queries. Almost all my BE's are SQL Server and the majority of my queries remain as Access querydefs against linked tables.

 

[Kayleigh]

The only point I want to make is that if you are converting from Jet/ACE to SQL Server et al, there is no need to convert all your queries also or think you have to convert to unbound forms. The vast majority of forms/queries will work fine as they are. That leaves you to concentrate on the ones that might benefit from using views or actually being converted to pass through queries. Almost all my BE's are SQL Server and the majority of my queries remain as Access querydefs against linked tables.

You are right about that. I have singled out the queries/forms which were running very slow and converting those to pass-through.

Been quite successful so far! Now I am looking to write some code to convert the simple queries to pass-through. Can anyone suggest how to change the connection string in vba please?

 

[theDBguy]

Can anyone suggest how to change the connection string in vba please?

Maybe something like?

Set qdf = db.QueryDefs("QueryName")
qdf.Connect = "NewConnectionStringHere"

 

[Isaac]

Maybe something like?

Set qdf = db.QueryDefs("QueryName")
qdf.Connect = "NewConnectionStringHere"

I find it unlikely that even the simple queries will not need additional editing for sql server anyway, so you are going to probably have to re-create them.

But you can also just copy and paste a pass-through query?