MySQL Bugs: #16834: Data not returned to Excel

Bug #16834	Data not returned to Excel
Submitted:	27 Jan 2006 10:15	Modified:	6 Nov 2007 11:52
Reporter:	Trevor Williams	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (Win XP)
Assigned to:		CPU Architecture:	Any

Description:
When trying to "Import Data", I fill in the "Data link properties" using the data source name of the local odbc dsn name, provide the correct user name and password as tested on the login of the MySQL Administrator tool, and also fill in the catalog name exactly as created in the administrator. When I click "Test Connection" it gives a "Microsoft Data Link Error" that says "Test connection failed because of an error in initialising provider. Catastrophic failure". The dsn connection had tested succesfully when created, although the form hangs after clicking the OK button the message box that reported success. 

If I try using the "New Database Query" option and click off the "Use the Query Wizard to create/edit queries", I can get into Microsoft Query, build queries and see the data in the tables. However when I select to return the data to Excel, it does not return any data and then Excel fails to respond, i.e. it hangs.

Is there any way to get this resolved or can you recommend a reporting tool that will use the MySQL database.

How to repeat:
Set up the system to get the "Test connection failed" message if you can.

If you provide the user name and password when creating the dsn with the
ODBC Windows manager are you able to make works with Excel?

I had specified the user name and password in the dsn previously. It fails without those details giving a "user cancelled" error. As I said the data gets to Microsoft Query but can't be passed back to Excel, as if the driver is being used differently by these two applications.

I tried using the debug option but it did not create any output files that I could find. Thanks for the prompt reply

Then there is a chance your issue is related to MS Jet engine. Please search
for the msjet40.dll and looking their properties see the version. Version
4.0.9025.0 is known for to have problems like this. I have 4.00.8618.0
and I don't have problems with Excel.

I have version 4.0.8618.0 of the msjet40.dll. I also found a msjet35.dll which I removed. I've also got msjetoledb40.dll on version 4.0.8227.0. 

I tried to return data to Excel after removing msjet35.dll but it is still the same.

An interesting discovery I just made is that if I terminate the process for MSQUERY in windows task manager while waiting for the data to get "returned to excel", the excel workbook then shows "Query from myodbc: Getting Data ..." but Excel stays hung like that and does not show the data.

Well we have the same Jet Engine stuff on our machines, then please provide
the table structure with some inserts and the MySQL server version are you
using, for to test on my side.

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Dear Friends,

Even i was getting the same error from months.
Also couldn't get any solution from most of the forums.

Finally i could debug it myself.

Error i Was Getting when trying to Get external data from new database query:
In the excel status bar it was showing "Waiting for data to be returned from microsoft Query" and the excel would hang.

Operating System Using : Windows XP
Microsoft Office Version : Office 2000
Oracle Database Using : Oracle 9i

Solution: (Am not sure if this works for every one, but no harm in trying)
1) Open a blank excel
2) Go to tools menu --> Click on Add-Ins
3) You would find MS Query Add-in and  ODBC Add-in 
4) Make sure to check the boxes against these 2 Add-Ins
5) Click OK and close the excel
6) Restart the system (Very important step)
7) Open a blank excel
8) Go to Data menu --> Get External Data --> New Database Query
9) Within few seconds --> Choose Data Source window will pop up

Regards
Chandru

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: "Test connection" button is removed from latest version and importing data to Excel works as expected with 3.51.21 and 5.1Alpha so I see no point in keeping this report open anymore.