SQL Pass-through Queries and List Boxes

[BeWyched]

I am re-engineering a large Access application to feed off a SQL Server
database.

The application has many instances where List Box RowSources are set as SQL
statements through VBA.

I wish the List Boxes now to be fed by pass-through queries to the SQL
database. Can I do this directly through VBA?

The only solution I've got so far is to create an empty pass-through query
using the query wizard, and set it as the List Box's RowSource. I can then
change the query's .SQL setting through VBA and re-query the List Box. This
works but is cumbersome.

Anyone any better ideas?

Thanks in advance.

BW

 

[Douglas J. Steele]

If you're trying to query SQL Server from Access, then you have no choice
but to use pass-through queries (such as you're doing), or creating linked
tables, which you can then use the same as any other tables. Pass-through
queries will be more efficient.

 

[BeWyched]

Thanks Douglas

Am I right in thinking that if I use linked tables, then the query will
still be executed on the local machine? If so then pass-throuh is the only
option as the sole purpose of the re-engineering is to improve performace.

I did an experement timing identical simple select queries being exectuted
using an Access back-end compared to SQL Server (both by telephone linked
remote access to identical database content). The Access route took some 12
seconds compared to 1/2 a second by SQL Server!! A complex query took for
ever on Access (timed out after a couple of minutes) but still less than a
second on SQL.

Re my question - is my method the best to use? ie. set the List Box's
Rowsource to a 'dummy' PT query, create the SQL statement using VBA and pass
this to the 'dummy's .SQL setting, then re-query the List Box?

Thanks.

BW

 

[Douglas J. Steele]

Yes, linked tables will be slower.

Is there a reason why you can't create permanent pass-through queries for
all of the list box's, rather than using dummy ones?

 

[BeWyched]

I will use permanent queries wherever possible.

However, there are quite a number of List Boxes where the structure changes
depending on a users choices. i.e. different fields are presented in
different orders.

Also, there are more where users have many options to add or filter data
fields including options to sort across different fields ( e.g. ' do you want
to sort by name or date'). I know that much of this can be built into the SQL
statement but it is significantly easier (for me anyway!) to dynamically
create the statements using VBA.

Thanks again.

BW

 

[Rick Brandt]

Thanks Douglas

Am I right in thinking that if I use linked tables, then the query will
still be executed on the local machine? If so then pass-throuh is the only
option as the sole purpose of the re-engineering is to improve performace.

You do not have to use a passthrough query to get server-side processing. You
only need to use a passthrough to *guarantee* server-side processing. Many
local queries against links will still be processed on the server. If your
query against the link is a basic select query then in all likelyhood the query
will be passed to the server and be processed there.

I did an experement timing identical simple select queries being exectuted
using an Access back-end compared to SQL Server (both by telephone linked
remote access to identical database content). The Access route took some 12
seconds compared to 1/2 a second by SQL Server!! A complex query took for
ever on Access (timed out after a couple of minutes) but still less than a
second on SQL.

A query against a link to an Access file (mdb) is not the same as a query
against an ODBC link to a SQL Server table. Have you tested the latter? I
doubt that you would find a significant difference between that and a
passthrough query.