Databases :: MySql :: How MySQL interperits "IN", work around? |
|||
By: GringoMike  |
Date: 23/07/2003 00:00:00  |
Points: 300 | Status: Answered Quality : Excellent |
|
Hey all, From what I've read in all my MySQL docs, I don't think I can use the IN keyword in a statement to direct the output of a query to a different database platform via ODBC. Can somebody verify this and/or tell me how to get around it. Sample code excerpt: ------ Set cn = New ADODB.Connection With cn .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};DATABASE=MyDB;" _ & "SERVER=localhost;UID=User;PASSWORD=Pass;OPTION=131075;" .Open End With cn.Execute "INSERT INTO [tblMySQL] IN " _ & "[ODBC;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\MySQL.mdb] " _ & "SELECT * FROM mysql_rcbounce" ------ Any idea how to accomplish this? Cheers, Mike |
|||
| By: VGR | Date: 23/07/2003 15:59:00 | |
Type : Comment |
| you don't need IN for this. Just qualify your table... cn.Execute "INSERT INTO " _ & "[ODBC;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\MySQL.mdb] " _ & ".[tblMySQL] SELECT * FROM mysql_rcbounce" |
|||
| By: GringoMike | Date: 24/07/2003 01:56:00 | |
Type : Comment |
| Unfortunately it's still not working. I'm getting a run-time error from MySQL saying to "check the syntax near..." (the ODBC portion). I also tried creating a DSN instead and got the same error. Interestingly I can flip the code around and open an Access connection, then execute the following without probs: ----- With cn .ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=d:\MySQL.mdb" .Open End With cn.Execute "SELECT * INTO [tblMySQL]" _ & "FROM [ODBC;DSN=rootcause].mysql_rcbounce;" cn.Close ----- Although I would rather do it the original way. Is it possible that the MySQL - SQL standards do not support this, or have you performed something like this before? Mike |
|||
| By: VGR | Date: 24/07/2003 04:49:00 | |
Type : Comment |
| your pronlem is ODBC+yourlanguage (VB?) it's rather trivial in SQL, MySql and PHP... |
|||
| By: GringoMike | Date: 24/07/2003 06:22:00 | |
Type : Comment |
| Yes I'm coding in VB. So if I were to enter the query directly into MySQL (without VB), you think it still can't handle the ODBC portion? Something like (in MySQL qry window): INSERT INTO [ODBC;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\MySQL.mdb].[tblMySQL] SELECT * FROM mysql_rcbounce Basically I just want to know if I can dump data directly from MySQL into Access. If it's not possible, that's ok. I just want to confirm :) Thanks, Mike |
|||
| By: VGR | Date: 24/07/2003 06:40:00 | |
Type : Answer |
| no, you can't from MySql directly. No database system (DB2, Oracle, SQL-Server etc) can do this. They can EXPORT/dump their data, but that's all... To make communicate two systems (namely Access and SQL-S or Access and MySql) you need a component, and that's what your MyODBC is :D However, MyODBC (and ODBC generally speaking, not counting the fact that it's soooooo slooooowwwwww) introduces limitations and oddities to your otherwise-perfectly-valid SQL code... Have you asked this in the Access section ? A lot of people believe that Access is a good front-end to MySql data... I disagree but it's a fact in the M$-world (people using VB and not Delphi, using SQL-Server and not MySql, using MFC and not Object Windows, etc) |
|||
| By: GringoMike | Date: 24/07/2003 06:59:00 | |
Type : Comment |
| I haven't asked in the Access TA because I figured the issue was on the MySQL side. But no need, you told me exactly what I wanted! Cheers, Mike |
|||
|
Do register to be able to answer |
|||
©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page!
Contact
- Privacy
- Contact us
- Member Agreement
- Jobs for our Experts
- our friend ActuLab
- Carcasherdotcom Seocontest
- Add Your Link
- Bee Web Directory
- Discover Directory
- FFind Directory
- Link Velocity Directory
- The Little Web Directory
- Red Links
- Tranix
- 2space Global Directory
Fully downloaded more than 1313 Million times
Copyright © 2003 - 2012 The IT European Experts Organization
last update on : 23/07/2008 09:41:27 GMT 0 (CEST)
