[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ACCESS to MYSQL Pass-Through Query

Posted on 2009-02-18
4
Medium Priority
?
769 Views
Last Modified: 2012-05-06
How can i create a dynamic pass-through query in MYSQL?

do i need to adjust my connection string?
ODBC;UID=root;PWD=test;DSN=localhost
0
Comment
Question by:CMDAI
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 23671501
I assume you are in Access creating a Pass Through query in there. Yes you can. The easiest way to get your ODBC connect string is to attach a table and then look at the Description property in the Access table design, the ODBC connect string is everything excep the TableNam at the end.

As far as the Dynamic bit then you may need to expand. You can not include parameters as everything in the Pass Through Query has to be Server related but you could modify the SQL in code.

Cheers, Andrew
0
 
LVL 1

Author Comment

by:CMDAI
ID: 23671851
For the first part i got it,
but im not so sure what u mean "the Pass Through Query has to be Server related"

I copied this from the linked "inventory table"
ODBC;DATABASE=jnbkcrop;DESCRIPTION=JNBKCorpLocalHost;DSN=JNBKCorpLocalHost;OPTION=0;PORT=0;SERVER=localhost;;TABLE=inventory

SQL:
SELECT ID, Title FROM jnbkcrop.inventory;

I get "Recordset is not updatable"
0
 
LVL 28

Accepted Solution

by:
TextReport earned 2000 total points
ID: 23672360
A Pass Through Query is not updateable. Your ODBC setting in the pass through query would be

ODBC;DATABASE=jnbkcrop;DESCRIPTION=JNBKCorpLocalHost;DSN=JNBKCorpLocalHost;OPTION=0;PORT=0;SERVER=localhost

The SQL in the pass through query is not interpreted / converted and is "PASSED THROUGH" as is to the server to run so the select below has to be in MySQL syntax rather than MSACCESS / JET syntax.

SELECT ID, Title FROM jnbkcrop.inventory

Cheers, Andrew
0
 
LVL 1

Author Comment

by:CMDAI
ID: 23672725
ahh, ok.
I was very much off on the meaning of passtrough q.
Thank's
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question