> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How Access Reads Data From SQL Server
- URL: https://nolongerset.com/how-access-reads-data-from-sql-server/
- Published: 2022-02-17T03:57:41.000Z
- Updated: 2026-05-08T12:59:35.000Z
- Description: 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.
- Author: Mike Wolfe
- Tags: SQL Server, Advanced, #Import 2026-05-20 02:59

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))

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/02/image-82.png)

I populated the data with one million rows. I used my [**NumToString()** function](https://nolongerset.com/converting-numbers-to-text/) to populate the *TextValue* field.

## Running the Test

I fired up the [**XEvent Profiler** in SQL Server Management Studio](https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-ssms-xe-profiler?view=sql-server-ver15). 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/02/image-81.png)

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/02/image-80.png)

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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/02/image-84.png)

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

```sql
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](https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/prepared-execution-odbc?view=sql-server-ver15), 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.

```sql
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...`: **prep**ares and **exec**utes 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`).

```sql
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:

```sql
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).

```sql
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:

```sql
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:

```sql
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.)

### External references

[Use the SSMS XEvent Profiler - SQL ServerThe XEvent Profiler displays a live viewer of extended events. Learn why to use this profiler, key features, and how to get started viewing extended events.Microsoft Docsyualan![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-ssms-xe-profiler?view=sql-server-ver15)

[Prepared Execution ODBC - ODBC API ReferencePrepared Execution ODBCMicrosoft DocsDavid-Engel![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/prepared-execution-odbc?view=sql-server-ver15)

[sp\_prepexec (Transact-SQL) - SQL Serversp\_prepexec (Transact-SQL)Microsoft Docsmarkingmyname![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-prepexec-transact-sql?view=sql-server-ver15)

[sp\_execute (Transact-SQL) - SQL Serversp\_execute (Transact-SQL)Microsoft Docsmarkingmyname![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-execute-transact-sql?view=sql-server-ver15)

*Image by [Gerd Altmann](https://pixabay.com/users/geralt-9301/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=2899900) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=2899900)*