Playing Telephone with SQL Server

Imagine you were in the crowd at Gettysburg listening to President Lincoln deliver a short speech.

Four score and seven years ago our fathers brought forth on this continent, a new nation, conceived in Liberty, and dedicated to the proposition that all men are created equal.

Unfortunately, you were so far back in the crowd you couldn't hear these now-famous words.  So you asked the person in front of you, "What did he say?"  The friendly chap shrugged his shoulders and shook his head, but then you saw him lean forward and whisper into the ear of the fellow in front of him.  That gentleman, equally perplexed, then leaned forward and whispered to the next man up.  

You watched as several more rows of observers repeated the ritual.  The slow-motion wave came to an end and reversed direction when someone finally whispered back Lincoln's now-immortalized opening line.  You followed the heads as they leaned back one at a time repeating the phrase to each successive person.  It was a bit hard to see, but each face scrunched up with increasing confusion until Lincoln's words reached your original confidant.  

Utterly befuddled, your new friend relayed the following profound words:

Forty-seven years ago my father bought me a train station.  He was incontinent.  He had dedicated his life to propositioning all men equally.

Several weeks later the original text of the speech appeared in the newspaper. Relieved upon reading it, you yelled over your shoulder to your wife, "Honey, it's OK!  The president's not losing his mind after all.  That speech makes a lot more sense now.  Turns out it was just another boring political talk.  Honestly, I don't know why Lincoln wasted everyone's time with this drivel.  Give it a month and it will be a distant memory."  

Nostradamus you were not.

The communication between Microsoft Access and SQL Server often carries a similar game-of-telephone quality to it.  

A simple query

For example, let's link to the following table, dbo.Account, in SQL Server:

The table has three rows of sample data.  Now, let's run the following simple SELECT statement in Access:

SELECT Account.AcctDesc, Account.InactivatedOn
FROM Account
ORDER BY Account.AcctDesc DESC;

That's a pretty basic query.  In fact, the syntax in that query is perfectly compatible with both Access and SQL Server.  One might reasonably expect, then, that Access would simply pass that query over as-is to SQL Server.  One would be wrong.

Lost in translation?

If we use XEvent Profiler or SQL Server Profiler to observe the actual T-SQL being executed on the SQL Server side, you might feel like you were right back in Gettysburg.  Here's what the above query produced in SQL Server:

SELECT "dbo"."Account"."AccountID" FROM "dbo"."Account" 
ORDER BY "dbo"."Account"."AcctDesc"  DESC 

declare @p1 int  
set @p1=NULL  
exec sp_prepexec @p1 output
  ,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int'
  ,N'SELECT "AccountID","AcctDesc","InactivatedOn"  FROM "dbo"."Account"  WHERE "AccountID" = @P1 OR "AccountID" = @P2 OR "AccountID" = @P3 OR "AccountID" = @P4 OR "AccountID" = @P5 OR "AccountID" = @P6 OR "AccountID" = @P7 OR "AccountID" = @P8 OR "AccountID" = @P9 OR "AccountID" = @P10'
  ,3,2,1,1,1,1,1,1,1,1  
select @p1

What in the world is going on here?  

A brief explanation

To help with our discussion, let's take a look at the contents of the Account table:

Access is retrieving the requested data in chunks of 10 records.  It accomplishes this in two separate steps.  The first step is to return the primary key in our requested sort order.  This would have resulted in the following values being returned: 3, 2, 1  ("Credit Card", "Check", "Cash").  

The next step is to return the fields we actually requested, beginning with the first chunk of 10 records.  The stored procedure sp_prepexec prepares and executes a parameterized T-SQL statement.  In this case, that T-SQL statement is this:

SELECT "AccountID","AcctDesc","InactivatedOn"  
FROM "dbo"."Account"  
WHERE "AccountID" = @P1
   OR "AccountID" = @P2 
   OR "AccountID" = @P3 
   OR "AccountID" = @P4 
   OR "AccountID" = @P5 
   OR "AccountID" = @P6 
   OR "AccountID" = @P7 
   OR "AccountID" = @P8 
   OR "AccountID" = @P9 
   OR "AccountID" = @P10

When it executes the above statement, it passes in the following values for @P1 through @P10: 3,2,1,1,1,1,1,1,1,1.  In other words, it's requesting the records with AccountID equal to 3, 2, or 1.

But why?

Access does this to provide the best performance in typical usage.  For instance, if you open an unfiltered form where the recordsource is a linked table with one million records, the data will display immediately because Access is only fetching ten records at a time.  It picks the first ten records being displayed and then fetches the rest in order.

That's why the record navigation number in the bottom left corner of a form initially shows only the current record number.  Depending on the size of the bound recordsource, the "of #" portion might take some time to fill in as all the records need to be retrieved before Access knows the final count.

The upshot

Not all usage is, in fact, typical.  So while the Access to SQL Server to Access secret handshake usually works out in your favor, sometimes it will blow up spectacularly.

You don't need to know how every single query gets translated between Access and SQL Server.  The important thing is to understand that every single query based on linked ODBC tables does get translated between Access and SQL Server.  

The next most important thing is to know how to retrieve that translation from SQL Server.  To do that, you'll want to familiarize yourself with SQL Server Profiler (which I've used for years) or the newer XEvent Profiler.

For a great primer on SQL Server Profiler, check out former Access MVP Philipp Stiefel's recent YouTube video on the topic:

Image by Steve Buissinne from Pixabay