Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Pass thorugh query to MySQL
- Thread starter PLiNk
- Start date
As long as you set up an ODBC connection, you should be able to use any of the major servers. We use Sybase and it works fine.
Afetr setting up the ODBC, go to link the table in and select ODBC connections as the File Type. Then on the Select Data Source window, select the Machine Data Source tab and select the appropriate database and then the table of choice when the list of available tables shows.
This is not a Pass Through query but it brings the table in so you can access it just like an access table.
You still may want to learn how to do a Pass Through query however as it will by-pass the Access data engine and speed things up. Really depends what you want from the server table though and how often.
Afetr setting up the ODBC, go to link the table in and select ODBC connections as the File Type. Then on the Select Data Source window, select the Machine Data Source tab and select the appropriate database and then the table of choice when the list of available tables shows.
This is not a Pass Through query but it brings the table in so you can access it just like an access table.
You still may want to learn how to do a Pass Through query however as it will by-pass the Access data engine and speed things up. Really depends what you want from the server table though and how often.
- Thread starter
- #3
As Toga has suggested you may want to learn to create a Pass-Thru query as there are advantages to using it vs. a ODBC linked table. You see the number of records that you are accessing from the server based table usually is much higher than what is being accessed in your ACCESS database.
Access queries using JET will try to read and download all of the records from the ODBC linked table before applying the criteria of the query. This many times is way more than your PC can handle. By create a Pass-through query and using the power of the MySQL server you are performing all of the heavy lifting(executing) with the more powerful computer system. Much more effecient.
It is pretty simple to create a pass-through query. Open a NEW query in the query design window. Close the table window prompt without selecting any tables. From the Query menu select SQL Specific and select Pass-Through. This will create a pass-through query with a SQL window for you to enter the SQL that will run on the MySQL system. Remember you must write the SQL in MySQL syntax NOT Access syntax. The pass-through query will just send the SQL string over the ODBC connection to the MySQL server and execute the SQL there.
Next in query design window select from the menu View/ Properties. This selection gives you the properties of this pass-through query. You can update the properties here to setup your query. The ODBC Connect string is important as you can store the ODBC database, user id, and password to connect to the mySQL server. Then you will not be prompted when the query runs. The other properties are important like max records, ODBC timeout, and whether to return records. you can look these up in Help for a complete explaination to make your choices.
Below is a sample ODBC connection string:
"ODBC;DSN=DNS name;DATABASE=databaseName;UID=UserID;PWD=password;"
Hopefully, this will get you started with pass-throughs. You can also update the SQL on the fly. I mean create a single generic passthrough query and use it for many situation. Just update the .SQL property of the saved query with the individual SQL necessary for the situation you want to run. You can do this by the following:
Dim db as DAO.database
db.QueryDefs("qryTempQuery"
.SQL = "SELECT A.* " & _
"FROM tblYourTable as A " & _
"WHERE A.ID = 123;"
db.close
DoCmd.SetWarnings False
DoCmd.OpenQuery("qryTempQuery"
DoCmd.SetWarnings True
The above demonstrates how you can modify or complete change the SQL property of a saved query and then run that pass-through query.
Post back with any questions.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
Access queries using JET will try to read and download all of the records from the ODBC linked table before applying the criteria of the query. This many times is way more than your PC can handle. By create a Pass-through query and using the power of the MySQL server you are performing all of the heavy lifting(executing) with the more powerful computer system. Much more effecient.
It is pretty simple to create a pass-through query. Open a NEW query in the query design window. Close the table window prompt without selecting any tables. From the Query menu select SQL Specific and select Pass-Through. This will create a pass-through query with a SQL window for you to enter the SQL that will run on the MySQL system. Remember you must write the SQL in MySQL syntax NOT Access syntax. The pass-through query will just send the SQL string over the ODBC connection to the MySQL server and execute the SQL there.
Next in query design window select from the menu View/ Properties. This selection gives you the properties of this pass-through query. You can update the properties here to setup your query. The ODBC Connect string is important as you can store the ODBC database, user id, and password to connect to the mySQL server. Then you will not be prompted when the query runs. The other properties are important like max records, ODBC timeout, and whether to return records. you can look these up in Help for a complete explaination to make your choices.
Below is a sample ODBC connection string:
"ODBC;DSN=DNS name;DATABASE=databaseName;UID=UserID;PWD=password;"
Hopefully, this will get you started with pass-throughs. You can also update the SQL on the fly. I mean create a single generic passthrough query and use it for many situation. Just update the .SQL property of the saved query with the individual SQL necessary for the situation you want to run. You can do this by the following:
Dim db as DAO.database
db.QueryDefs("qryTempQuery"
.SQL = "SELECT A.* " & _"FROM tblYourTable as A " & _
"WHERE A.ID = 123;"
db.close
DoCmd.SetWarnings False
DoCmd.OpenQuery("qryTempQuery"
DoCmd.SetWarnings True
The above demonstrates how you can modify or complete change the SQL property of a saved query and then run that pass-through query.
Post back with any questions.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
- Thread starter
- #5
Thank you for your very helpful post scriverb!
But I am still at odds with the actual connection working, even though I have done everything leading up to the point where the driver is trying to contact the mysqlserver.
Connection fails and I don't know what to do.
I am pretty sure that the problem has to do with the odbc drivers or some incompability
(level 0 -> level 1? I don't know what this means or may cause) therein.
However, I've been far to busy arranging different activities that I haven't been working all that much on it, but I felt a reply was timely.
regards
PLiNk
But I am still at odds with the actual connection working, even though I have done everything leading up to the point where the driver is trying to contact the mysqlserver.
Connection fails and I don't know what to do.
I am pretty sure that the problem has to do with the odbc drivers or some incompability
(level 0 -> level 1? I don't know what this means or may cause) therein.
However, I've been far to busy arranging different activities that I haven't been working all that much on it, but I felt a reply was timely.
regards
PLiNk
- Status
Similar threads
- Question
- Question
