Queries and ODBC

Hi

When an Access query is run against an ODBC table, is it possible in
any way to tell what is passed to the server? I have a remote Sybase
database linked into Access and the bandwidth is terrible, so I need
the processing to be done purely on the server. Some simple queries
run quickly, so the processing is being done on the server. Some
complex queries with IIf statements etc. are definately not being
converted to Sybase compatible SQL and I think therefore that Access
is requesting the entire tables to be sent down the network and Access
then locally performs the query.

So I would like to be able to tell which queries will be accepted by
the server and which won't

thanks
R.

re: Queries and ODBC

"R" <rasimmonds@hotmail.com> wrote in message
news:6e0be8aa.0312050308.5b8d7e03@posting.google.c om...[color=blue]
> Hi
>
> When an Access query is run against an ODBC table, is it possible in
> any way to tell what is passed to the server? I have a remote Sybase
> database linked into Access and the bandwidth is terrible, so I need
> the processing to be done purely on the server. Some simple queries
> run quickly, so the processing is being done on the server. Some
> complex queries with IIf statements etc. are definately not being
> converted to Sybase compatible SQL and I think therefore that Access
> is requesting the entire tables to be sent down the network and Access
> then locally performs the query.
>
> So I would like to be able to tell which queries will be accepted by
> the server and which won't[/color]

If you need server-side processing, make the queries pass-thru. They'll
have to be rewritten with Sybase compatible SQL


[color=blue]
> thanks
> R.[/color]

re: Queries and ODBC

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Generally, if you want the query to be performed exclusively on the
Sybase server you CANNOT use Access functions or user-defined VBA
functions in a query. If you're JOINing Access tables to Sybase table
you should not use Access functions/user-defined VBA functions on any
Sysbase column.

You might wish to try creating separate queries: pass-thru queries to
get the Sybase data & Access queries that read the pass-thru queries
and to further refine the data.

- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9Dms4echKqOuFEgEQKsmwCfWjXYxOVx8qNZzpZIPQHleG 8Pp5UAoNuL
dSjXqtSey9NzpAbub1FgCAAE
=ifcJ
-----END PGP SIGNATURE-----

R wrote:
[color=blue]
> Hi
>
> When an Access query is run against an ODBC table, is it possible in
> any way to tell what is passed to the server? I have a remote Sybase
> database linked into Access and the bandwidth is terrible, so I need
> the processing to be done purely on the server. Some simple queries
> run quickly, so the processing is being done on the server. Some
> complex queries with IIf statements etc. are definately not being
> converted to Sybase compatible SQL and I think therefore that Access
> is requesting the entire tables to be sent down the network and Access
> then locally performs the query.
>
> So I would like to be able to tell which queries will be accepted by
> the server and which won't
>
> thanks
> R.[/color]

re: Queries and ODBC

there's a registry entry key under DEBUG, called JETSHOWPLAN which
when turned on will create a text file with the jet query optimizer
plan

this will show you what part of a query is [remotely] handled and
what part is handled by JET

the trick is to understand how to build an equivalent sybase query (or
stored procedure) and use a jet passthrough query to execute it on the
server

rasimmonds@hotmail.com (R) wrote in message news:<6e0be8aa.0312050308.5b8d7e03@posting.google. com>...[color=blue]
> Hi
>
> When an Access query is run against an ODBC table, is it possible in
> any way to tell what is passed to the server? I have a remote Sybase
> database linked into Access and the bandwidth is terrible, so I need
> the processing to be done purely on the server. Some simple queries
> run quickly, so the processing is being done on the server. Some
> complex queries with IIf statements etc. are definately not being
> converted to Sybase compatible SQL and I think therefore that Access
> is requesting the entire tables to be sent down the network and Access
> then locally performs the query.
>
> So I would like to be able to tell which queries will be accepted by
> the server and which won't
>
> thanks
> R.[/color]

re: Queries and ODBC

thanks Roger- exactly what I was looking for!