Pass through query

[Paradigm]

I want to create a pass through query in Access to get data from a MYSQL
table. I need to do this using code so that sertain aspects of the query can
be changed.
When I look at the SQL version of a Select and a Passthrough query in access
they appear to be the same.
eg.
"SELECT * FROM MYTABLE WHERE MYFILED = TRUE"
What do I need to do to make this a pass through query?
Any help appreciated.
Alex

[Thomas Bartkus]

On Sun, 23 Jan 2005 13:34:28 +0000, Paradigm wrote:

I want to create a pass through query in Access to get data from a MYSQL
table. I need to do this using code so that sertain aspects of the query can
be changed.
When I look at the SQL version of a Select and a Passthrough query in access
they appear to be the same.
eg.
"SELECT * FROM MYTABLE WHERE MYFILED = TRUE"
What do I need to do to make this a pass through query?
Any help appreciated.

You create a passthrough query in Access by declaring it as such. From
the query designer, you right click and select
[SQL_Specific]_[Passthrough] from the sub menu. This will put you right
into the SQL text window. Here you want to right click on the title bar
and set your ODBC connection string. Set [Returns Records]=TRUE for
SELECT queries.

The thing to know about Passthrough queries is that you are bypassing the
Microsoft Jet interpreter and passing your query directly to MySQL. This
means that you will need to use strict MySQL syntax. While similar,
MySQL SQL syntax is not quite the same as Microsoft's.

In particular, MySQL has no true boolean type that understands
(TRUE,FALSE) which means your statement "WHERE MYFILEID = TRUE" would fail
in a passthrough query.

I might suggest -

If you are just starting with MySQL, you could just link the MySQL table
using the linked table manage you can invoke by right clicking in the
Tables panel. Once you create your linked table, you can create
ordinary Access queries that work against your MySQL table. The ODBC
driver will handle syntax translation for you.

When you understand MySQL a bit better, you will then be able to use
passthrough queries to your advantage.

Thomas Bartkus


The
thing to remember when using passthrough queries

[Paradigm]

I use the code
Set qrydef = CurrentDb.CreateQueryDef(myQueryName, myQueryDef)
and myQueyDef is something like
"SELECT * FROM MYTABLE WHERE MYFIELD = somevalue"
This creates a query in Access but it is a select query. What do I need to
do to make it a pass through query.
Alex

"Thomas Bartkus" <th***********@comcast.net> wrote in message
news:pa****************************@comcast.net...

On Sun, 23 Jan 2005 13:34:28 +0000, Paradigm wrote:

I want to create a pass through query in Access to get data from a MYSQL
table. I need to do this using code so that sertain aspects of the query can be changed.
When I look at the SQL version of a Select and a Passthrough query in access they appear to be the same.
eg.
"SELECT * FROM MYTABLE WHERE MYFILED = TRUE"
What do I need to do to make this a pass through query?
Any help appreciated.

You create a passthrough query in Access by declaring it as such. From
the query designer, you right click and select
[SQL_Specific]_[Passthrough] from the sub menu. This will put you right
into the SQL text window. Here you want to right click on the title bar
and set your ODBC connection string. Set [Returns Records]=TRUE for
SELECT queries.

The thing to know about Passthrough queries is that you are bypassing the
Microsoft Jet interpreter and passing your query directly to MySQL. This
means that you will need to use strict MySQL syntax. While similar,
MySQL SQL syntax is not quite the same as Microsoft's.

In particular, MySQL has no true boolean type that understands
(TRUE,FALSE) which means your statement "WHERE MYFILEID = TRUE" would fail
in a passthrough query.

I might suggest -

If you are just starting with MySQL, you could just link the MySQL table
using the linked table manage you can invoke by right clicking in the
Tables panel. Once you create your linked table, you can create
ordinary Access queries that work against your MySQL table. The ODBC
driver will handle syntax translation for you.

When you understand MySQL a bit better, you will then be able to use
passthrough queries to your advantage.

Thomas Bartkus


The
thing to remember when using passthrough queries

[Bill Karwin]

Paradigm wrote:

This creates a query in Access but it is a select query. What do I need to
do to make it a pass through query.

I'm not a MS Access user, so I can't speak from experience.
Does this web page help?
http://support.microsoft.com/?kbid=303968

I also found a topic in the MS Access Help titled "Send commands to an
SQL database using a pass-through query". Try searching the Help for
"pass through".

Regards,
Bill K.