MySQL Bugs: #23122: Accessing View from MS-Access

Bug #23122	Accessing View from MS-Access - losing joined fields
Submitted:	10 Oct 2006 6:20	Modified:	13 Oct 2006 7:11
Reporter:	David Boccabella	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	ODBC 3.51.12 MySQL 5.0.22 Comm-nt	OS:	Windows (Windows 200+)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC, VIWS

Description:

I have this view

DELIMITER $$;

DROP VIEW IF EXISTS `sap2k_dat`.`v_item_sales`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_item_sales` AS (select `items`.`ItemNumber` AS `ItemNumber`,`items`.`ItemName` AS `ItemName`,`items`.`BaseSellingPrice` AS `BaseSellingPrice`,`taxcodes`.`TaxCode` AS `TaxCode`,`items`.`MYOBItemID` AS `MYOBItemID` from (`items` join `taxcodes` on((`items`.`SellTaxCodeID` = `taxcodes`.`TaxCodeID`))) order by `items`.`ItemNumber`)$$

DELIMITER ;$$

And it works using SQLYog. However in MS-Access it is not return the TaxCode field which is a joined table

This is causing a LOT of issues as it means that viewsd are not possible to use effectively

How to repeat:

Create the view.

Use SQLYog to connect to display the view. Note the number of fields

Open MS Access and link to the view. Any fields that are on the joined table (Taxcode) are missing

Hi David and thanks for your problem report. Can you please attach dump with tables, view and some data (preferably created with mysqldump) so I can test?

Dear Tonci

I have some interesting news for you.

I was trying to generate an example of the faulty view on my local machine and when I connected MS-Access to the view it worked as expected. All fields were there.

I then connected to the remote database that was causing me the issues with the view - and the view also ran correctly showing ALL fields.

I then logged onto the remote server and ran the view using SQLYog - and the view worked correctly

Then I opened MS-Access and ran the view - and the view was missing columns from the left join.

I checked version of MS-Access (both the same), versions of MySQL (both Same) and finally versions of the MyODBC (both same 3.51.12)

However when I compared the sizes of the MyODBC3.DLL between my local machine and the server I notices that the server's one was older and it's size was larger.

I downloaded and uninstalled/reinstalled MyODBC. The MyODBC3.DLL on the server remained the same size with the same date.

I unlinked and reattacted the view into MsAccess and the issue still remained.

Then I renamed the MyODBC3.DLL to MyODBC.Dll.Bak and copied the version from my local machine upto the server.

I then ran MS-Access on the server - dropped and re-linked the view AND IT WORKED!!!!!

Now - I have uploaded the 2 files. The BAK is the one that does NOT work and the straight DLL is the one that does work.

Can you find out what the difference is so that this can help other users. Both files say that they are version 3.51.12

Many thanks for your help

Dave

David, great it works now!

> Now-I have uploaded the 2 files. The BAK is the one that does NOT work and the straight DLL is the one that does work.

David, what's the URL?

Hi Tonci

Ah - I tried to load them to here   ftp://ftp.mysql.com/pub/mysql/upload/ 

And it looks like the load failed at your end.

Do you have an email I can send them to as they are about 2meg zipped

Many thanks

Dave

Hi Dave. Please retry with ftp. Give archives meaningfull names and send me full URL (ie. ftp://ftp.mysql.com/pub/mysql/upload/bug23112working.zip).

Hi Tonci

Here is a link to the files

http://www.cvsol.com/public/bug-data-23122.zip

Something else I have been working on that will help MS-Access users.

When MS released the latest version of msJet40.dll folks found that when they tried to link to MySQL via ODBC - they got an error.

There have been many suggested fo\ixed for this - usually copying over the older DLL and trying...... to get Windows not to overwrite it again.

A solution that I found on the net was to copy the MsJET40.dll into the Access directory and also to add in this empty file  msaccess.exe.local  into the same directory.

This forces Access to use the local copy of the MsJET40.dll and just fixes the issue.

Now I have written a small VB program that will do all of this for you and the installation is loaded here

http://www.cvsol.com/public/MySql_AccessFix.zip

I'll also be puttng this up on the forums for others to use.

Take Care

Dave

David, thanks for your help and your interest in MySQL. Your findings might help a lot of people on the forums.
I checked your libraries and the "wrong" one is definetly too big for "normal" build and too "small" for debug build. Where did you get it from?

Hi Tonci

If you mean the 'normal'  one was the one marked 'Bak'.  That came from, you downloadable 3.51.12 EXE Link from the MySQl.Com website - MySQL Connectors

The smaller version that works..  I wish <sighs> I could remember as I have downloaded several version and might have picked it up from elsewhere.  Hence my thanks for the developer in the forums.

As for helping folk.. Something I enjoy doing. I'm pretty active in several forums (not the MySQL one as my knowledge of things there are limited) but I am always willing to help others either solve a problem or to understand concepts better.

Take care and I DO hope you can find out where my version came from.  If you want I'm happy to do any tests with the MyODBC as I know the steps to test them now.

Dave

David, I meant smaller one (not .bak) but anyway, thanks for your help. I will forward your offer to connector/ODBC team.

I'm not sure why this has been marked as "not a bug" - I'm having what seems to be the identical problem with 3.51.14, except that playing around with the MS Jet DLL as described above doesn't affect the problem.

Replacing my MyODBC3.dll file with the original submitter's "good" version 3.51.12 DLL DOES fix the problem though. It seems pretty clear that there's some kind of issue in the MyODBC3.dll library or in the installation process.

Before finding this bug report, I had reinstalled the MyODBC connector from the current copy on the MySQL web site without any effect.

Garth, you will have to better than this... As for why I closed this report, the reason is obvious, some mirrors had MyODBC.dll that was not built properly and we do not know where it came from.