UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Closed TopicStart new topic
> Refuting myths about Access Front-End + ODBC-linked Tables    
 
   
vtd
post Dec 25 2006, 07:26 AM
Post#1


Retired Moderator
Posts: 19,667
Joined: 14-July 05



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.

Let me say this: they 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. I should point 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 when I
trace the opening of a Form in one of my databases.

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:
CODE
SELECT FF.*
FROM tblFactFormulation AS FF
Order BY "FF"."BaseFormCode" ,"FF"."FactFormID"

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.
CODE
declare @P1 int
set @P1=37
exec sp_prepexec @P1 output, N'@P1 int',
N'SELECT "FactFormID","frg_FactoryID","BaseFormCode","SeqNoPerBF",
"FactFormCode","OldFactFormCode","MixingTemp","CoolingTemp","ActiveStatus",
"frg_FactFormStatsID","frg_ColourID","frg_ApplnID","FFAppln","ProductRange",
"ReasonForChange","Notes","ApprvOffNoteDT",
"DateCreatedRec","DateUpdated","DateArchived","upsize_ts"  
FROM "dbo"."tblFactFormulation"  WHERE "FactFormID" = @P1', 7
select @P1

Aha, 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.
CODE
SELECT "FF"."FactFormID"
FROM "dbo"."tblFactFormulation" "FF"
WHERE (("frg_FactoryID" = 1 ) AND NOT(("ActiveStatus" = 0 ) ) )
ORDER BY "FF"."BaseFormCode" ,"FF"."FactFormID"

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.
CODE
declare @P1 int
set @P1=37
exec sp_prepexec @P1 output, N'@P1 int',
N'SELECT "FactFormID","frg_FactoryID","BaseFormCode","SeqNoPerBF",
"FactFormCode","OldFactFormCode","MixingTemp","CoolingTemp","ActiveStatus",
"frg_FactFormStatsID","frg_ColourID","frg_ApplnID","FFAppln","ProductRange",
"ReasonForChange","Notes","ApprvOffNoteDT",
"DateCreatedRec","DateUpdated","DateArchived","upsize_ts"  
FROM "dbo"."tblFactFormulation"  WHERE "FactFormID" = @P1', 57
select @P1

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.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 03:55 PM