[AccessD] Problem of a listbox's response on network... Part 1

<<The main reason for this is that when querying a SQL back end from Access,
the entire dataset is sent to the front end, which then applies the WHERE
and ORDER BY predicates locally. When using a PTQ, Access gets out of the
way and leaves the heavy lifting to the back end, which then sends only the
final resultset to the front end.>>

umm...that's not quite true.  JET will pass a SQL statement off to a SQL
backend when it can, pass-through or not.

As long as you stay away from joins to local tables, VBA calls, or JET
specific SQL, it will pass it off.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, January 26, 2014 01:34 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem of a listbox's response on network... Part 1

Mark,

Yes and no. If you're using dynamic SQL, assembling the query by hand and
then executing it, then for sure you have a rewrite on your hands. But if
you use named queries, life gets a lot easier. Pass-Through Queries (PTQs)
to the rescue! In essence, it boils down to this.

a) Grab the syntax from your named query.
b) Create a Stored Procedure or View on the back end using the same syntax.
You might have to adjust it here and there to make it run. For example, you
won't be able to use Access functions in the back end.
c) Create a PTQ in the front end that points to the SP or View you just
created
d) In your Access code, use the syntas "CALL myPTQ."

A few months back I worked on a project using an Access front end and a
MySQL back end. The whole thing had been written to do all the queries in
the front end. We identified a number of areas where a speed gain might be
most profitable and began there, following the steps outlined above. The
results were dramatic, to say the least -- sometimes a tenfold increase in
performance.

The main reason for this is that when querying a SQL back end from Access,
the entire dataset is sent to the front end, which then applies the WHERE
and ORDER BY predicates locally. When using a PTQ, Access gets out of the
way and leaves the heavy lifting to the back end, which then sends only the
final resultset to the front end.

Arthur


On Sun, Jan 26, 2014 at 1:11 PM, Mark Simms <marksimms at verizon.net> wrote:

> Thanks Jim - and this implies anyone migrating from MDB to Oracle or SQL
> Server has a complete rewrite on their hands as far as VBA-based data
> retrieval, correct ?
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com