This page requires general cleanup in formatting or text to better fit the UA Wiki Guidelines

Related Content:
    Beginners Guide To ODBC

We often hear that using Access and ODBC-linked Tables from Microsoft SQL Server is very inefficient and myths such as:

• All records are sent to the Access Front-End client if we use a linked Table (or a Query based on the linked Tables) as the RecordSource of the Form.

• Lots of field values need to be sent to the Access Front-End for filtering to be processed in the Front-End.

persist in different Access forums.

But these are just MYTHS. JET works extremely well with the ODBC driver for Microsoft SQL Server. JET does NOT retrieve all records just because the ODBC-linked Table (or the SQL using linked Tables) is used as the RecordSource of the Form. Most "reasonable" selection criteria, i.e. criteria that JET knows that Microsoft SQL Server can handle, are sent to Microsoft SQL Server for processing, not in the Access Front-End as often stated. It should be pointed out that Microsoft SQL Server is not aware of most (if not all) VBA / Access VBA functions so criteria that use VBA functions are not "reasonable" and cannot be passed to Microsoft SQL Server for processing.

To see how JET uses the ODBC-linked Table(s) from SQL Server, one of the tools we can use is the SQL Profiler. Here is the sequence of how JET retrieves the data.

Set-up: 1. A simple Form to show Factory Formulations (for mixing PVC compounds used in PVC pipe extrusion). There are 4 Factories and each has a number of Factory Formulations.

2. Each Factory is allowed to see all, including those belonging to other Factories but in most cases, each Factory only wants to see its own set of Factory Formulations.

3. In addition, each Factory Formulation can be marked as active or archived. For normal operations, they are only interested in active Factory Formulations but occasionally, they need to see the archived Factory Formulations also.

4. Thus, the RecordSource has no selection criteria and in the Form_Load Event the Filter is set to filter for the current Factory (from the UserName) and active Factory Formulations (the user's normally relevant set of Factory Formulations). On the Form, a ComboBox allows the user to select another Factory and an OptionFrame allows the use to select "active", "archived" or "both".

5. The RecordSource SQL is basically:

[tblFactFormulation] is an ODBC-linked Table from the SQL Server Back-End.

SQL Profiler Trace:

When tracing the SQL Server processes using the SQL Profiler, the relevant traces, i.e. requests received (and actions done) by the SQL Server are as follows:

1.

FactFormID is the PrimaryKey of the Table [tblFactFormulation]. Clearly, Access / JET decides to get the values of the PK Field only for further processing before asking for more data even though the RecordSource askes for all Fields.

The first value in the returned Recordset is FactFormID = 7.

2.

So, JET asks for all Field values of ONLY ONE Record (FactFormID = 7) which is going to be the current record for the Form. Well, at least until the Form_Load Event code is processed. The filter set in the Form_Load sents the next request to SQL Server (frg_FactoryID = 1 & "active" Factory Formulations only).

3.

Same as point 1. Access / JET decides to get only the PK values before deciding on what to get next. Note that the selection criteria are sent to SQL Server!

The first value in the returned (fitered) Recordset FactFormID = 57.

4.

Once again, JET only asks for all Field values of ONLY ONE record which is potentially the current record for the Form (and eventually is).

The above sequence hopefully refutes the myths that Access / JET retrieves all Records if an ODBC-linked Table (or a Query / SQL based on linked Tables) is used as the RecordSource of a Form or lots of data need to be transferred to the Access Front-End as the filtering is done on the Front-End.