Queries and ODBC - Microsoft Access / VBA

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.

Nov 12 '05 #1

Subscribe Reply

1853

DFS

"R" <ra********@hot mail.com> wrote in message
news:6e******** *************** ***@posting.goo gle.com...

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
If you need server-side processing, make the queries pass-thru. They'll
have to be rewritten with Sybase compatible SQL
thanks
R.

Nov 12 '05 #2

MGFoster

-----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/AwUBP9Dms4echKq OuFEgEQKsmwCfWj XYxOVx8qNZzpZIP QHleG8Pp5UAoNuL
dSjXqtSey9NzpAb ub1FgCAAE
=ifcJ
-----END PGP SIGNATURE-----

R wrote:

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.

Nov 12 '05 #3

Roger

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

ra********@hotm ail.com (R) wrote in message news:<6e******* *************** ****@posting.go ogle.com>...

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.

Nov 12 '05 #4

thanks Roger- exactly what I was looking for!

Nov 12 '05 #5

Similar topics

6764

Bad performance in queries with Jet4.0 and linked ODBC-tables to SQL-Server 2000

by: Andreas Lauffer | last post by:

I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:

Microsoft SQL Server

4024

Can't see all of the queries when merging to MS Word and can't get to reports

by: Jerry Hull | last post by:

I'm working with a database developed by an untrained person over several years - and on a network that has recently been upgraded with a new server installed and MS office upgraded from 2K (I think - it might have been XP) to 2003. The database is impressive, both in what it does and the obtuse and inconsistent ways it works. There are several hundred queries, for example, with no indication of where they are used or if they are in fact...

Microsoft Access / VBA

21612

pass-through queries and parameters

by: Zlatko Matiæ | last post by:

Let's assume that we have a database on some SQL server (let it be MS SQL Server) and that we want to execute some parameterized query as a pass.through query. How can we pass parameters to the server ? Is it possible to use parameters in pass-through queries ? An additional question: Is it possible to connect to a database on MySQL or PostgreSQL using ADO ? Is it possible to execute pass-through queries with parameters, using ADO...

Microsoft Access / VBA

3557

MS Access, Oracle 9i, security, and pass-thru update queries

by: DFS | last post by:

Architecture: Access 2003 client, Oracle 9i repository, no Access security in place, ODBC linked tables. 100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters and ReadOnly. Each group sees a different set of menu options when they open the client and login to Oracle. For the sake of speed I use pass-through queries here and there for updates and deletes. I update their SQL property in code and execute them.

Microsoft Access / VBA

1969

MS Access 2002 problem: linked form fields in ODBC queries

by: monnomiznogoud | last post by:

Ok, my problem is the following: I have very complicated Access 97 databases that link through ODBC to Sybase databases. Now in some of the forms controls I had queries that used as "where clause" parameters form field values; For example: select foo from bar where a_colmun = !!

Microsoft Access / VBA

1273

How can I do amalgamate 3 select queries and then get unique entries from the result

by: Astra | last post by:

Hi All Strange request I know, but could somebody give me pointers on how I can put 3 queries into 1 'thing' and then get only the unique entries from this 'thing'. To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queries themselves aren't that complex and all return the same 2 fieldsets of stock code and stock desc. Because these separate queries might bring back the same stock code/description I need to amalgamate...

Microsoft SQL Server

1567

action queries not working when migrating to mainframe?

by: Henrootje | last post by:

I have an existing frontend, connecting to a .mdb to a folder. Now I am wondering whether it would be wise to move the tables in the backend to a DBMS. A colleague tells me that in that case, we have to rewrite all our actionqueries. Select queries would just use the linked tables in the tables screen but all of the actionqueries would have (extensively) to be rewritten into passthrough queries?

Microsoft Access / VBA

377

Connection pooling issue with pass through queries

by: Bill E. | last post by:

I'm creating an MS Access application that connects to a SQL Server 2005 database using pass-through queries and ADO to call stored procedures. I ran a SQL trace and started clicking through various forms to look at how things were being handled on the database. I noticed an alarming number of connections were opened as if there was absolutely no connection pooling. After several clicks I had 20 new connections. It appeared that a new...

Microsoft Access / VBA

6317

Pass-through queries fail even after SQL Server reconnected

by: marshmallowww | last post by:

I have an Access 2000 mde application which uses ADO and pass through queries to communicate with SQL Server 7, 2000 or 2005. Some of my customers, especially those with SQL Server 2005, have had pass-through queries fail due to intermittent connection failures. I can easily restablish a connection for ADO. My problem is with pass-through queries.

Microsoft Access / VBA

8356

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8551

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7386

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6198

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5664

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4198

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2771

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2011

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1776

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General