Can someone explain to me why Jet issues those ODBC commands?

[BananaRepublic]

I'm trying to understand how Jet behaves with ODBC sources. I've logged the
SQL recieved by the server and while I think that Jet acts generally
intelligently when we want to navigate a bound form or update/insert/delete a
record. It is a good citizen in those scenarios, only selecting few rows for
the navigation, and using WHERE clause to guarantee that it works with one or
few rows at a time.

However, when I open a form (in this scenario, a simple bound form with a
simple linked subform), I get several queries, which seems to be redundant.
The form's recordsouce:

Main form's recordsource: SELECT * FROM country;
Subform's recordsource: SELECT * FROM city;

Linked Fields:

(Master) Code = (Child) CountryCode

*******************Start Log***********************
071115 10:56:56 78 Connect root@localhost on world
78 Query SET SQL_AUTO_IS_NULL = 0
78 Query SELECT Config, nValue FROM MSysConf
78 Query SELECT `Code` ,`Name` ,`Continent` ,`Region` ,`SurfaceArea` ,
`IndepYear` ,`Population` ,`LifeExpectancy` ,`GNP` ,`GNPOld` ,`LocalName` ,
`GovernmentForm` ,`HeadOfState` ,`Capital` ,`Code2` FROM `country`
78 Query SELECT `ID` ,`Name` ,`CountryCode` ,`District` ,`Population`
FROM `city`
78 Query SELECT `ID` ,`Name` ,`CountryCode` ,`District` ,`Population`
FROM `city`
78 Query SELECT `country`.`Code` FROM `country`
78 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,
`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,
`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `country` WHERE
`Code` = 'ABW'
78 Query SELECT `city`.`ID` FROM `city` WHERE ( 'ABW' = `CountryCode` )
78 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM
`city` WHERE `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` =
129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129
78 Query SELECT `country`.`Code` FROM `country`
78 Query SELECT `city`.`ID` FROM `city` WHERE ( '' = `CountryCode` )
78 Query SELECT `city`.`ID` FROM `city` WHERE ( '' = `CountryCode` )
78 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,
`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,
`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `country` WHERE
`Code` = 'ABW'
78 Query SELECT `city`.`ID` FROM `city` WHERE ( 'ABW' = `CountryCode` )
78 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM
`city` WHERE `ID` = 129
**************End Log***************

Specific questions:

1) Why ask for a full table scans even if it already has the keys it needs?
Indeed, why ask for it multiple times?

2) Putting in TOP values does not work. Why?

3) Putting in WHERE values does seems to help in limiting the recordset, but
Jet still will try to retrieve the whole recordset. Why can't Jet just fetch
the first few rows?

4) Can I improve the behavior somehow?

TIA.

 

[BananaRepublic via AccessMonster.com]

Addendum:

1) Pertaining to my 3rd question, I forgot to clarify whether it is possible
to bind a form to a whole table (of course using query), which logically mean
no WHERE clause. Can this still be done without requiring a full table scan?
I could of course write some VBA routines to dynamically limit the recordset,
but wanted to make sure I wasn't over complicating anything.

2) Microsoft's white paper on Jet's behaviors with ODBC (
http://support.microsoft.com/kb/128385, for version 3.0, but I understand
this still is valid for 4.0) mentions that when a dynaset is used, Jet will
only retrieve the unique identifying columns, which I can see it doing when
the database opens up and repairs its connection string. But this isn't being
done when a form is opened. Furthermore, it's supposed to fetch only 100 rows,
but I see nothing like this in SQL log.

3) Ignore my question #2- I forgot that ODBC didn't support TOP, and issuing
the command would require a full table scan.

 

[Albert D. Kallal]

Have you tried setting the sub form record source when the main form loads.

eg:


me.MySubForm.form.RecordSource = "select * from city"

Also, are you using the link master/child settings?

You could simply try :

me.MySubForm.form.RecordSource = "select * from city where id = 129"

 

[BananaRepublic via AccessMonster.com]

Good question.

I think I already re-set recordset (not recordsources) for both forms on main
form's open event. That was suggested from this article:
http://support.microsoft.com/kb/248011

But I will go and experiment with whether resetting recordsources instead of
recordsets makes a difference.

 

[BananaRepublic via AccessMonster.com]

All right.

I commented out all code behind the form, and left the links in subform
control blank, and set the recordsource as thus:

Main form: SELECT country.Code, country.Name, country.Continent, country.
Region, country.SurfaceArea, country.IndepYear, country.Population, country.
LifeExpectancy, country.GNP, country.GNPOld, country.LocalName, country.
GovernmentForm, country.HeadOfState, country.Capital, country.Code2, * FROM
country;

Subform: SELECT city.ID, city.Name, city.CountryCode, city.District, city.
Population, * FROM city WHERE (((city.CountryCode)=Forms!country!Code));

This is the SQL output when the form is opened:

************Start of Log***************
071116 9:09:28 134 Query SELECT `country`.`Code` FROM `country`
134 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,
`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,
`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `country` WHERE
`Code` = 'ABW'
134 Query SELECT `city`.`ID` FROM `city` WHERE (`CountryCode` = 'ABW' )
134 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM
`city` WHERE `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` =
129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129
134 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,
`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,
`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `country` WHERE
`Code` = 'ABW'
*************End of Log***************

It seems that the culprit may lie with the code where I set form's recordset
to the recordset of SQL. Commenting out the lines where I set the recordset,
I get this SQL output:

************Start of Log***************
071116 9:15:22 134 Query SELECT `Code` ,`Name` ,`Continent` ,`Region` ,
`SurfaceArea` ,`IndepYear` ,`Population` ,`LifeExpectancy` ,`GNP` ,`GNPOld` ,
`LocalName` ,`GovernmentForm` ,`HeadOfState` ,`Capital` ,`Code2` FROM
`country`
134 Query SELECT `city`.`ID` FROM `city` WHERE (`CountryCode` = 'ABW' )
134 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM
`city` WHERE `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` =
129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129
*******************End of Log***************

In both scenarios, Jet still performs a full table scan *then* selects for
few rows after. If I'm reading ODBC whitepaper right, Jet should already had
loaded the indexes it needs (when Access started up), not for every time a
form is opened, no?

In first log, you can see it scanning the full table for the index columns,
but doesn't do that in second log when the code was uncommented back again
except for the lines setting the recordsets.

If I insert a WHERE clause, Jet won't perform a full table scan, but what if
I wanted to be able to access the entire recordset, but just not all at once?

 

[BananaRepublic via AccessMonster.com]

After reading some more documentations, it looks like in order to bind a form
to a recordset, whether DAO or ADO, one need to use a client-side cursor. If
I am understanding the documentation correctly, this will send the entire
recordset over the wire, which is not ideal. Setting a cachesize (the
documentation did not indicate clearly if cachesize will work for a client-
side cursor) seemed to do nothing.

Do I need to write a function or something in order to ensure that only few
records are retrieved, stored in a local cache and as user navigates,
retrieve for more records?

Is there any other ways I can do to ensure no full table scans are being
requested?