Solved Pass-through queries in SQL server (2 Viewers)

[Pat Hartman]

I find it unlikely that even the simple queries will not need additional editing for sql server

The vast majority of my applications use SQL Server, DB2, Oracle, etc. as the BE. The BE RDBMS depends on what the client currently supports for their other applications.

Applications I design with ACE tables initially convert cleanly to SQL Server in an afternoon because I use good client/server practices when I build the application whether I intend to convert it in a month or maybe never. The only reason it takes a few hours rather than minutes for the conversion is because extensive testing is required to ensure that nothing is missing in the code.

In all the years I've been using Access since 1993, I can count on one hand the number of unbound forms I needed to create and on two hands the number of reports that needed stored procedures to make them efficient. Usually, the biggest change is adding a View to make joins more efficient. Using a view doesn't make the query not updateable so there is no problem with keeping your forms bound.

Some queries like bulk deletes, should always be pass through because The transaction Access surrounds them in adds a lot of overhead. Using a pass through query eliminates that overhead so you won't get the question at the end. Do you want to delete x records? They're just gone. Certainly less safe but also significantly faster. Better still, if you are deleting all rows, just use Truncate instead. Happens n a blink.

 

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

 

[Pat Hartman]

Jet and ACE make every effort to make every query a "pass though" query. i.e. send the query to the server and wait for the results to come back. There is certainly some overhead because Jet/ACE is involved but in exchange, you get to use standard (albeit a very old level of functionality) and bound forms. If you don't understand how this process works, you can get in the way and prevent Access from ever passing a query to the server. Instead, Access will send queries that request entire tables which it will then process locally. This is the worst possible scenario. You are getting NO benefit from having a "real" server based data store and you are flooding the LAN with unnecessary traffic.

I understand Issac's dissatisfaction with the QBE. However, since i also use saved querydef's almost exclusively, I don't have to look at what the QBE does to my lovely, formatted SQL. The whole point of the QBE is to give you a point and click interface. This is something I dreamed about from the early 80's when I first started working with DB2. The pain of having to type all SQL by hand was just a PITA. Plus, since I work with multiple clients at one time, I might have a dozen active databases in some state of construction and that means I have hundreds of tables and thousands of column names swirling around in my head when I go to type something. The QBE with all its flaws is a huge time (and error) saver should you choose to use it. So, my perspective on the whole SQL thing is totally different. I spent YEARS of coding SQL by hand and hating every minute of it, trying to keep all the table and column names straight and get the string correct in one compile rather than four. Being basically a lazy person, when I discovered Access in the early 90's and discovered that I could link to and update DB2 tables on the mainframe, I thought I had died and gone to heaven. All the tedium of having to create queries by hand and all that background work that Access does for you when you are using bound forms took the weight of the world off my shoulders and I could concentrate on the specifics of an application rather than the infrastructure. So a CICS transaction that might take me three days to code and unit test could frequently be done in a couple of hours using VBA and bound forms. And that's with me having lots of code libraries to pull common code from so I didn't always have to write it all from scratch.

It never ceases to amaze me that people actually want to write embedded SQL by hand. They don't want to point and click and build reusable querydefs. I have to admit, going from doing it all myself to the QBE was a much bigger step than going from COBOL where I did it all myself to VBA and bound forms. So, here's the bargain I made with the QBE, it builds the select clause -always and it builds any straightforward where clause. If the Where clause is too hard to understand in the QBE, I switch to SQL view and rewrite it without all the extraneous garbage needed to make the graphic view easy for MS to create. As long as I save the querydef in SQL View and avoid switching to QBE view and accidentally saving, I can have my cake and eat it too. Although, I copy these strings into a table to save them because if I make a mistake and Open the QBE for that query in Design view rather than SQL view, Access has a tendency to "help me out" and rewrite the SQL. However, if you carefully avoid QBE view for those queries, it doesn't mess with your formatting. And the table is my "safety play" (bridge term). If the where clause gets messed up, i don't have to retype it again. There is NO advantage that I can see for embedding static SQL in VBA. Period. I do create SQL using VBA but that is for situations where it is dynamic rather than static. ALL static SQL lives as a querydef. And perforce, all dynamic SQL is created with VBA although sometimes the basic SELECT part of the query is a querydef and only the WHERE clause and ORDER BY are built in VBA.

And one final benefit of sticking with plain vanilla Access SQL whenever possible rather than pass through queries and stored procedures, I can swap BE's in a heartbeat. I have a couple of applications that I sell as packages. They are very expensive and they are not "shrink-wrapped" so they come with a certain level of support including the client gets to choose the BE he wants and I only have to maintain one FE regardless of whether the BE is SQL Server, DB2, Oracle, or ACE. In one of the apps, I needed to make dual procedures because ACE is a little different in some areas from the big guys but all the big guys work with CodeB and ACE works with CodeA This code was very specific to the application. For training purposes, the app included a set of training data and since the user would update it each time they trained new users, I needed a way to restore it back to its original version so I kept backup tables in the FE with the test data. They were in the FE rather than the BE because they might change with each version of the FE and it was too hard to update the client's BE remotely. ACE allows you to insert rows with autonumbers populated but I had trouble with SQL server. I needed to set Identity Insert off and on to put back the original data including their PKs.

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

Only if the tables do not have primary keys or if you have done something funky. Most queries used as the RecordSources for bound forms will only have ONE table that gets updated. Others are there for lookup purposes only so they never cause Cartesian Products. I have queries with a dozen or more joins that are updateable because they don't break any of the rules that would make them not updateable.

 

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

 

[Pat Hartman]

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.

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.

I work on waaaaaaaaaaaay too many databases to be able to keep all the table and column names in working memory so give me the QBE

 

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

 

[Pat Hartman]

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.

I will not debate the merits of Access SQL which is mired in 1989 (with an option to upgrade to 1992 which if taken, will break existing queries) vs T-SQL or any other SQL variant. Access SQL is the base SQL language implementation for a reason. That reason being it needs to work with ALL variants of SQL or ODBC becomes impossible to manage. Every manufacturer of a RDBMS adds its own tweaks and cool stuff to its language implementation. ODBC converts from the Access SQL variant to whatever the target RDBMS requires. Oracle has stuff that SQL Server doesn't have and vice versa or implements the same advanced feature differently. Many SQL books include several variants where you can see the subtle differences in how the same feature is implemented. I'm sure that MS could update the base language provided the ANSI committee (American National Standards Institute) has updated it. But Access SQL can NEVER be better than the lowest common denominator defined by ANSI. I think the reason that Access SQL was never updated is because changes were made that would cause existing SQL to break if they were implemented. Even just going to the 1992 level breaks older queries. Backward compatibility is very important to a tool like Access. ANSI doesn't care about backward compatibility, they are all about theory and standards rather than the practical world of an existing code base. MS tracks a lot of what goes on in Access so they may be able to tell what features developers aren't using. If no one is checking the use 1992 version feature, they would be less inclined to offer newer options.

Access querydefs do not support multiple SQL Statements because they are substitutes for tables. Pretty much anywhere you can use a table, you can substitute a query and that would not be possible if the querydef supported multiple SQL statements. There is no inherent benefit to being able to have multiple SQL statements in a single file. You can always create a sp or UDF (User Defined Function) to run multiple queries.

If you actually need an SQL feature not supported directly by Access, then you need to use a Pass Though query.

I don't have a problem coming up with the object names.

Good for you. If I designed the database, I have a lot better shot at coming up with needed column and table names off the top of my head from the thousands floating about but too much of my work involves supporting applications built by others or built by me years ago. A client recently called me to add some features to a db I build for them 6 years ago and haven't looked at since. Other developers have different views on name construction. I am always happy if they were simply consistent in applying whatever standards they had and avoided embedded spaces and special characters. Clients don't pay me to clean up stuff that isn't broken so I work with what I have. If the developer actually used standards and they are identifiable, I don't deviate from them when adding new tables and columns and other objects. There is nothing worse than having to work with an app that uses conflicting standards. So if they abbreviate Number as No, so do I even though I hate it.

We're going to have to agree to disagree on the usefulness of a GUI tool to build SQL statements especially with Access. People who are not using ODBC RDBMS gain no advantage whatsoever by writing embedded SQL. One of the reasons is because the first time a querydef is executed, the database engine builds an execution plan and saves it whereas every time any embedded SQL is executed, Access must perforce recreate the execution plan since it has no place to store it. In earlier versions of Access this was much more of a problem than in newer versions. It takes a fair amount of workspace to calculate the execution plan and older versions of Access did this in such a way that you needed to compact the db to shrink it back to its previous size almost every day. So over the course of a day, running hundreds of queries could cause substantial bloat. Luckily for us, MS did fix that problem. But we are still left with the ever so slight speed hit for each query caused by recalculating the plan for how to execute the query. Not noticeable normally in todays fast computers so the technical differences between querydefs and embedded SQL are marginal these days. So for me it comes down to the convenience of being able to build the majority of most queries using point and click and the ability to reuse queries easily and have one place to change them rather than having to change them in the embedded code wherever they are used. The fact that Access messes up my neat SQL when I choose to write it by hand is irrelevant. I have developed a way to beat Access when I need to and still take advantage of the tool it offers.