SQL Server

How Access Reads Data From SQL Server

How does Microsoft Access pull data from SQL Server? You'll be amazed when we pull back the curtain to expose how Access interacts with SQL Server.

Mike Wolfe

Feb 16, 2022 • 7 min read

Did you ever wonder how Access is able to jump to the end of a really big linked table in SQL Server without taking forever to get there?

If you've never pulled back the curtain on how Access interacts with SQL Server, you're in for a surprise. Prepare to have your mind blown.

A Large Table

For testing purposes, I created a one million-row table (cleverly named LargeTable) in SQL Server and linked to it from Microsoft Access.

The table has two fields:

A primary key named NumericValue (int)
A text representation of the number named TextValue (nvarchar(255))

I populated the data with one million rows. I used my NumToString() function to populate the TextValue field.

Running the Test

I fired up the XEvent Profiler in SQL Server Management Studio. This tool let me spy on the T-SQL calls coming from Access.

Back in Access, I double-clicked on LargeTable to open it in datasheet view. The datasheet displayed the first 57 records.

I then clicked on the far-right nav button to jump to the end of the table. The datasheet showed the last 56 records (plus the new record placeholder), beginning with record 999,945.

Here are the screenshots from Access:

The top of the table. Notice that the bottom row has the value 57.

The bottom of the table. Notice that the top row has the value **999,945**.

T-SQL Calls Logged in SQL Server

Here are the relevant calls to SQL Server:

Fetch the Primary Key Values

The first call, highlighted in purple, is a simple SELECT statement that returns the primary key value for every record in the table.

SELECT "dbo"."LargeTable"."NumericValue" FROM "dbo"."LargeTable"

This is one of the reasons why I prefer to use an autonumber primary key, rather than a natural key or a GUID-based key. Autonumber keys are very efficient in terms of data storage. These sorts of performance considerations become especially important when your data is stored in the cloud.

Even if you don't have an autonumber field, it's still critical to designate some kind of unique index on the table. Without a unique index, I assume Access is forced to fetch every field in every row, as that's the only way it can guarantee that it is displaying the correct records.

Compile and Execute Query Plan 1

The next call, highlighted in green, uses the sp_prepexec stored procedure to compile a query that selects a single row from the table.

When a query is compiled, SQL Server generates an optimal query execution plan. This takes a bit of time upfront, but it results in better performance every time the query is executed.

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "NumericValue","TextValue"  FROM "dbo"."LargeTable"  WHERE "NumericValue" = @P1',1
select @p1

There are four statements in the above block:

declare @p1 int: declares a T-SQL variable named @p1 as an int (the data type that matches our table's primary key); the @p1 variable will receive the query handle, which is a unique identifier used to reference compiled query plans
set @p1=NULL: initialize the @p1 variable
exec sp_prepexec...: prepares and executes the query plan
... @p1 output, ...: @p1 is a return variable that will be populated by the sp_prepexec statement with the handle that corresponds to the query plan
... N'@P1 int', ...: the leading "N" indicates that what follows is a unicode string; the string contains all the parameters associated with the query plan...in this case there is only one: @P1
... N'SELECT "NumericValue","TextValue" FROM "dbo"."LargeTable" WHERE "NumericValue" = @P1', ...: the T-SQL statement to be executed; note that @P1 refers to the @P1 in the immediately preceding argument to sp_prepexec; it does NOT refer to the @p1 declared at the beginning of this T-SQL block
... 1: the final argument to sp_prepexec is the literal value assigned to @P1, which will be used in the initial execution of the query
select @p1: retrieves the query handle assigned to the query plan for future reference (the returned value is 1, which we can infer from later calls to sp_execute)

Compile and Execute Query Plan 2

The next call, highlighted in yellow, is functionally similar to query plan 1 above.

The big distinction is that this compiled query accepts ten different parameters (@P1 - @P10). Those ten parameters are declared in a single literal string (N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int'). However, they get called individually at the end of the sp_prepexec statement (,1,2,3,4,5,6,7,8,9,10).

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 "NumericValue","TextValue"  FROM "dbo"."LargeTable"  WHERE "NumericValue" = @P1 OR "NumericValue" = @P2 OR "NumericValue" = @P3 OR "NumericValue" = @P4 OR "NumericValue" = @P5 OR "NumericValue" = @P6 OR "NumericValue" = @P7 OR "NumericValue" = @P8 OR "NumericValue" = @P9 OR "NumericValue" = @P10',1,2,3,4,5,6,7,8,9,10
select @p1

Populate the First Set of Visible Rows - 10 at a Time

Once the query plans have been compiled, Access begins fetching records ten at a time until it fills the initial datasheet view.

Access calls sp_execute, passing it the query plan handle (2) and then the parameters associated with that query plan. For example, the first row in the block of sp_execute statements below will execute the following query:

SELECT "NumericValue","TextValue"  
FROM "dbo"."LargeTable"  
WHERE "NumericValue" = 11 OR "NumericValue" = 12 
   OR "NumericValue" = 13 OR "NumericValue" = 14 
   OR "NumericValue" = 15 OR "NumericValue" = 16 
   OR "NumericValue" = 17 OR "NumericValue" = 18 
   OR "NumericValue" = 19 OR "NumericValue" = 20

If you recall from earlier, there were 57 visible rows when the datasheet opened. As you can see below, Access fetches records 10 at a time until it has enough data to fill all 57 rows (with three additional rows cached for when the user starts scrolling through the datasheet).

exec sp_execute 2,11,12,13,14,15,16,17,18,19,20
exec sp_execute 2,21,22,23,24,25,26,27,28,29,30
exec sp_execute 2,31,32,33,34,35,36,37,38,39,40
exec sp_execute 2,41,42,43,44,45,46,47,48,49,50
exec sp_execute 2,51,52,53,54,55,56,57,58,59,60

Populate the Last Set of Visible Rows - 10 at a Time

After clicking the button to jump to the end of the table, here's what Access sent to SQL Server:

exec sp_execute 1,1000000
exec sp_execute 1,1000000

exec sp_execute 2,999852,999853,999854,999855,999856,999857,999858,999859,999860,999861
exec sp_execute 2,999945,999946,999947,999948,999949,999950,999951,999952,999953,999954
exec sp_execute 2,999955,999956,999957,999958,999959,999960,999961,999962,999963,999964
exec sp_execute 2,999965,999966,999967,999968,999969,999970,999971,999972,999973,999974
exec sp_execute 2,999975,999976,999977,999978,999979,999980,999981,999982,999983,999984
exec sp_execute 2,999985,999986,999987,999988,999989,999990,999991,999992,999993,999994
exec sp_execute 2,999995,999996,999997,999998,999999,1000000,1000000,1000000,1000000,1000000
exec sp_execute 2,999862,999863,999864,999865,999866,999867,999868,999869,999870,999871

First, it fetched the final row of the table (exec sp_execute 1,1000000). This sp_execute call ran the following query, as originally defined in Query Plan 1 above:

SELECT "NumericValue","TextValue"  
FROM "dbo"."LargeTable"  WHERE "NumericValue" = 1000000

Access knew that the NumericValue of the last row was 1,000,000 because of the very first query it ran that returned every NumericValue value from the entire table.

Next, Access began fetching records ten at a time, as it had earlier when we first opened the table in datasheet view. This time, though, it was pulling records from the end of the table.

Recall that the top visible value in datasheet view was 999,945. If you look at the sp_execute 2 calls, you'll notice that they start from that first record and increment by ten until the end of the table is reached. (There are also two somewhat rogue calls mixed in to grab records 999852 - 999871; I'm not sure what that's all about.)