Access XP / MySQL / MyODBC: Access' caching system?

Hi all,

While working on an Access UI to a MySQL database (which should be a
reasonable, low-cost, flexible interface to the DB, better than
web-based, much less costly than a full-fledged .NET app or so.... is
it?), I tried measuring the bandwith consumed by the
Access/MyODBC/MySQL link, which came out to be, er, quite high.

I fancied it would be interesting to look at the queries Access throws
at MySQL through the ODBC link, so I set up a query log for MySQL. It
now appears that Access tries to cache the records in the database, but
it does so in quite a weird way.

The db is accessed through a normal (one-record view) form, linked to a
query, linked to a "linked table". When I go to record #5 (of 5000) in
the form, a whole bunch of queries is executed (because of a subform,
but there also appears a bunch of lines in the log like "SET AUTOCOMMIT
= 0" every time I scroll... Pretty strange, this is a huge overhead).
What stroke me most is that Access seems to fetch records 41 to 50.
When I press PgDn again, scrolling to record #6, Access fetches 51 to
60, and it fetches 61 to 70 for record #7 on the screen, and so on.
Scrolling back to #5 makes Access fetch 41 to 50 again.

Is there some ridiculously failing caching system at work here? Is
there a way to disable it? I read some things about CacheSize; how
should I use it in a form with an updatable query?

Thanks for your time; this seems to be the final big problem until we
can start working seriously on this system, so I'd be really glad if I
could fix this :)

Yours sincerely,


Onno

.

Relevant Pages

  • Re: Fetch 6 records from a table randomly
    ... critical details (e.g. what sort of table? ... "I have a table 'documents' stored in a MySQL database that I'm accessing ... I haven't been able to find a function in Ruby to pick a random element. ...
    (comp.lang.ruby)
  • Re: sarge + postfix + mysql
    ... >So when postfix/courier is doing a mysql database lookup, ... >'vmail' that is only allowed to log in from localhost and has no ... >permissions for accessing the database 'provider'. ...
    (Debian-User)
  • RE: CGI::Session
    ... > some hashes in a MySQL database, ... > other visitor preferences like the font style, colors, font ... > in a MySQL record than the session ID? ... transmitted without the written permission of the copyright owner. ...
    (perl.beginners)
  • Re: sarge + postfix + mysql
    ... If you are using postfix 2.2.x the sintax of the .cf files may be different. ... > So when postfix/courier is doing a mysql database lookup, ... > permissions for accessing the database 'provider'. ...
    (Debian-User)