Access ODBC Linked Tables Bug – Assigning a Recordsource at runtime disables ODBC Multi-Row-Fetch for continuous form

by Philipp Stiefel, originally published 2020-11-20, last updated 2020-11-20

Image by Richard Chalmers from Pixabay, used here under Pixabay License

Dear reader, looking at the title of this text you might ask what this is about. And you are right. This is an exotic topic that will not affect many Microsoft Access developers, less will care about the issue, and probably even less will understand what this is about from the title alone.

The compelling reason (for me) I write about this is: You are not going to read about this anywhere else!

The behavior this is about is not even officially documented anywhere in the Microsoft Access documentation. Nevertheless, I dare to classify this as a bug, because it is a solid case of inconsistent and undesirable behavior.

The behavior I call “ODBC Multi-Row-Fetch” is based on the useMultiRowFetch option of the CRecordset which I assume is a base type of the DAO.Recordset we use a lot in Access.

The feature and the problem it solves – Short Explanation

Generally, when a client process communicates with a remote server, it is usually much more efficient to send fewer big chunks of data back and forth than many smaller ones. The main reason for that are network latency and transport protocol overhead.

An example to understand this problem: Imagine you got 100 printed pages to send to someone. What is more efficient? Putting each page into an individual envelope, running to the post office with one letter at a time posting it, then running back getting the next letter and running to the PO again, repeating this until you posted them all, or putting all 100 pages into one big envelope, going to the post office once and being done with it?

If we look at the DAO.Recordset in our more specific Access (client) and SQL Server scenario, this means it is much more efficient to fetch 100 rows from the server in chunks of 10 records each than fetching 100 records each on its own. – This is what ODBC Multi-Row-Fetch is all about.

The Access Bug

A client reported a performance problem with an Access continuous form based on a query on an ODBC linked table. The form’s Recordsource property is blank at design time. It is bound to the data source by a SQL statement assigned dynamically at run time to the he Recordsource of the form.

When doing this, the problem manifests itself in noticeable delays in updating the displayed data on screen when scrolling in the form. This also creates an unpleasant flickering effect while scrolling.

I was able to identify a major cause of the problem in ODBC Multi-Row-Fetch being disabled for continuous forms when the Recordsource is not defined in the form properties at design time but only dynamically at run time.

The problem affects all versions of Microsoft Access at least from Access 2007 up to current Access 2016/365 V2008.

Reproducing the Bug

Here are the steps to reproduce the issue.

Step 1 - Create a simple table on SQL Server and populate it with some data

Step 2 - Link this table to an Access accdb database. The ODBC driver used appears to be irrelevant. I tested with the old “SQL Server” driver as well as the most recent “ODBC Driver 13 for SQL Server”.

Step 3 – Create a continuous form based on that table. One automatically created by the “Multiple Items” wizard will do.

Step 4 – Start SQL Server Profiler, connect to the SQL Server, and start a New Trace capturing SQL send from Access to the server.

Step 5 – Open the form in Access.

Step 6 – Observe: Access uses ODBC Multi-Row-Fetch to fetch 10 rows at a time. In SQL Server Profiler this looks like this:

In essence: Two SQL statements are sent to the server to fetch the first 20 rows for the dynaset type recordset.

Step 7 – Open the form in design view. Clear the Recordsource property. Set the Recordsource dynamically with VBA. E.g. in Form_Load:

Step 8 – Save the form design changes, close the form, open the form again.

Step 9 – Observe: Access does not use ODBC Multi-Row-Fetch anymore but fetches each row on its own. In SQL Server Profiler this looks like this:

[Truncated here, goes on until record 20. The first record is sometimes fetched multiple times.]

In essence: Now 20+ SQL statements are sent to the server to fetch the first 20 rows for the dynaset type recordset.

Further Analysis

The above is the most basic way to reproduce the problem. It exists all the same if it is an ODBC table or view. It also does not make any difference whether just the table/view name is used or a full SQL Select statement.

Data Sheet forms appear to be not affected by the problem, only continuous forms.

The performance differences are not severe in a fast, local network. However, the problem becomes very annoying when there is some network latency between the frontend and the SQL Server, e.g. when the SQL Server is hosted on Azure.

Workarounds for the issue

I found one workaround myself, which resolved the issue for me. However, I was wondering whether there was a better solution. So, I also asked a group of reputable Access experts if they were aware of the problem and know of other solutions or workarounds. – Nobody was aware of the problem, but there were two alternative suggestions for workarounds.

Bind the Form to a Recordset

My own workaround for the problem, and the one which I’m most comfortable implementing is this:

The problem does not exist if you do not put the SQL statement as text into the Recordsource property of the form, but if you open a DAO.Recordset with the very same SQL and then bind the Recordset to the form using code similar to this:

With this approach Access consistently uses ODBC Multi-Row-Fetch to get the data.

Use a Stored Query

(Suggested by Ben Clothier and Dirk Goldgar)

Instead of setting the form’s Recordsource to a raw SQL statement, you put your SQL in a stored Access query. Then you assign the query name to the form’s Recordsource. When required, you modify the SQL text of the stored query to change the executed SQL, finally you need to reassign the very same query to the form Recordsource to trigger the form to requery its data.

With this approach Access also consistently uses ODBC Multi-Row-Fetch to get the data.

So, this is a complete workaround for the problem. I personally find this approach somewhat unwieldy because it requires the additional query. Nevertheless, it's good to have this alternative available.

Use a dummy Recordsource

(Suggested by Anders -The Smileycoder- Ebro)

Instead of leaving the Recordsource blank at design time, set it to a dummy SQL statement returning no data. E.g. "SELECT ID, DummyData FROM tblTest WHERE 0=1" and change this to "SELECT ID, DummyData FROM tblTest" in the Form_Load event of the form.

With this approach all records, which are initially visible on the form when it opens, are still fetched with the inefficient single row queries. However, as soon as you start scrolling down in the form, Access switches mysteriously from single row queries to fetching multi-row batches.

This is not the perfect solutions, as it only mitigates the problem when you start scrolling, but on the plus side, you just enter the dummy Recordsource in the form property sheet and do not need to worry about anything when writing VBA code. So, this is probably the easiest workaround for now.

Share this article: