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.

 

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

 

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

 

[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?

 

[Isaac]

Perhaps my perspective is a bit limited, because I rarely write pass-through queries against SQL Server with raw SQL in them (similarly to how I don't very often use SQL Queries on SQL Server using saved raw code either), I instead convert anything to be used > once into a procedure and simply execute it from Access. Random saved blocks of sql code in the sql server environment is not commonly allowed in well-controlled environments, and I stay away from it even in - and perhaps especially in - pass through queries where Access is further obfuscating the organization of the general repository.

Thus, everything tends to get re-written, as I'm obsessive (almost) about SQL formatting.

Between that, and, my preferences with regard to Access' unnecessary semicolons, excessive unnecessary bracketing and my preferred alias style, there would be almost no queries that I would just dump Access SQL into SQL Server sql and call it a day.

But yeah, if you stick to ANSI sql to begin with, you'll have less conversion work to do.

Then again - the goal of sticking to easily-convertable ANSI sql in order to paste into another platform someday, that goal takes a very distant "second" to the goal of optimization according to each individual platform at the time, so, I probably wouldn't be thinking much about it anyway.

Using any multi-table recordsource, whether a t-sql View or an Access query, often makes it non-updateable.

You make a good point about bulk deletes.
And in t-sql, even it's also good to delete large amounts of records in a loop of chunks, if truncate cannot be used, which avoids temp bloat.

 

[Isaac]

I meant t-sql, not access sql, when it comes to formatting and everything else i said about writing sql by hand.

Writing real t-sql in a normal sql server environment - SSMS - is a totally different world and literally can't even be compared to Access in almost any way, apples and oranges. And yes, hands down, writing t-sql by hand in ssms is way better than anything access offers.
But, if you aren't really in the sql server world that much, the access solution seems good, just a matter of perspective.
For access the access qbe is great, since access HAS no IDE to speak of for writing sql.
For sql server, SSMS is better than "anything", including any drag and drop stuff.

There are a million ways in SSMS by creative copy, pasting, right-click-script-to, and replace (ctrl+h) to quickly write thousands of lines...far faster than even access qbe can.

All I meant is for any sql that belongs on sql server - i.e., things you have chosen to actually write IN t-sql, then in that case, I would never write them in Access, and I don't think hardly any database developer with a sql server background would. now someone will say they do of course.

 

[Galaxiom]

I agree and I dislike writing T-SQL for this reason. SSMS is missing a QBE tool. It has something but it seems to only work to create queries and I can only find it some of the time. Maybe it only works for views. I've never been able to figure out how to use it to update an existing query. It probably works this way because otherwise it would have to do the same bad stuff that Access' QBE does to rewrite the SQL to make it easier to build the graphic view.

The QBE in MSSQLSMS is lame. Many of the constructs supported by TSQL are not even available in it. Using it to edit a query that it cannot support can corrupt the query.

There are a lot of features that make working in the SQL editor very efficient. Firstly the Intellisense of course. It is constantly prompting with names of objects in the current context of what you are typing. This includes the columns and aliases from subqueries. It doesn't just do partial matches from the start of the name. It will offer options where the typed characters are in the middle of the name.

Using Schemas can narrow down the list of objects too.

I work in several databases. One has hundreds of tables including one with several hundred columns. I don't have a problem coming up with the object names. Objects from any database including those on Linked Servers can be referenced from any query without linking tables.

Anything that is not valid in the context is underlined in red. Errors are indicated by bars in the vertical scroll bar so are always obvious no matter where you are in the window. It also shows colour codes for what has been edited and what had been saved.

You can drag the Column heading from the table or view in the Object Explorer and it will drop a list of the columns into the SQL text.

The text editing is very powerfully featured such as being able to select while holding down ALT and affecting all selected rows. Great for adding the table name to a column list.

Multiple queries can run at the same time. A query can be set to run and then edited while it is running as the results are still appearing.

Temp tables and Common Table Expressions (like a temporary view that runs at the start of the query) are just created inline and can be referred to throughout. Multiple commands and queries can run inside the one query.

It has simple functions like ROW_NUMBER that are a miserably slow PITA to achieve in Access. The equivalent of Allen Browne's ConcatRelated is just simple standard tool in TSQL from 2017 on.

The Express version supports 10GB databases. The full featured, unlimited (except for distribution) Developer version completely free. Any Access developer not using SQL Server really doesn't know what they are missing out on.