MySQL Bugs: #14461: Cannot link Created View

Bug #14461	Cannot link Created View
Submitted:	29 Oct 2005 10:05	Modified:	31 Oct 2005 19:10
Reporter:	hsquare Lim	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hi 

I am a newbie to mysql.
I actually want to create a View and link it to my MS access database.

However, I am not able to view the table from Access.

I have tried it using the Mysql query and I was able to see the field I wanted.

Please advice.

I am using Mysql 5.0.12 and MyODBC 3.51.12.

Thanks

How to repeat:
Hi 

I am a newbie to mysql.
I actually want to create a View and link it to my MS access database.

However, I am not able to view the table from Access.

I have tried it using the Mysql query and I was able to see the field I wanted.

Please advice.

I am using Mysql 5.0.12 and MyODBC 3.51.12.

Thanks

Sorry... I was able to create link to view from Access without problems. Could you please write here table and view definition  what you can't create link ?

Sorry.

My view is as follow.
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `media`.`qry4retall` AS select `w`.`Withdrawn_No`
AS `Withdrawn_No`,`y`.`Media ID` AS `Media ID`,`m`.`Category` AS `Category`,`m`.`Drawer` AS `Drawer`,`m`.`Rows` AS `Rows`,
`y`.`WithdrawfrLib` AS `WithdrawfrLib`,`y`.`Returned` AS `Returned`,`y`.`Authoriseby` AS `Authoriseby`
from ((`media`.`withdraw` `w` join `media`.`withdrawndetail` `y` on((`w`.`Withdrawn_No` = `y`.`Withdrawn_No`)))
join `media`.`media` `m` on((`y`.`Media ID` = `m`.`Media ID`)))

The table structure of the 3 tables are as follows.

Media 
CREATE TABLE `media` (
  `Media ID` varchar(50) NOT NULL default '',
  `Media_Type` varchar(20) default NULL,
  `Category` varchar(20) default NULL,
  `System` varchar(50) default NULL,
  `Value date` datetime default NULL,
  `Description` varchar(200) default NULL,
  `Library Location` varchar(40) default NULL,
  `Drawer` varchar(5) default NULL,
  `Rows` varchar(5) default NULL,
  `Purpose` varchar(150) default NULL,
  `Withdrawn` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY  (`Media ID`),
  KEY `CategoryMedia` (`Category`),
  KEY `Library Location` (`Library Location`),
  KEY `media tableSystem` (`System`),
  KEY `MediaMedia_Type` (`Media_Type`),
  KEY `SystemMedia` (`System`),
  KEY `Value date` (`Value date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Withdraw
CREATE TABLE `withdraw` (
  `Withdrawn_No` varchar(50) NOT NULL default '',
  `Year` year(4) default NULL,
  `Location` varchar(10) default NULL,
  `Withdrawn_ID` int(10) default NULL,
  `WithdrawDate` datetime default NULL,
  `WithdrawTime` datetime default NULL,
  `WithdrawType` varchar(50) default NULL,
  `Requestby` varchar(250) default NULL,
  `WithdrawReason` longtext,
  `Withdrawby` varchar(50) default NULL,
  `ApprovedBy` varchar(50) default NULL,
  `ReturnDate` date default NULL,
  `ReturnTime` datetime default NULL,
  `ReturnBy` varchar(50) default NULL,
  `Returned` tinyint(1) unsigned NOT NULL default '0',
  `Acknowledgedby` varchar(50) default NULL,
  PRIMARY KEY  (`Withdrawn_No`),
  KEY `Location` (`Location`),
  KEY `Returned` (`Returned`),
  KEY `Withdrawn_ID` (`Withdrawn_ID`),
  KEY `WithdrawType` (`WithdrawType`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

WithdrawnDetail
CREATE TABLE `withdrawndetail` (
  `Withdrawn_No` varchar(50) default NULL,
  `Media ID` varchar(50) default NULL,
  `WithdrawTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `WithdrawfrLib` varchar(50) default NULL,
  `WithdrawfrDrawer` varchar(50) default NULL,
  `WithdrawfrRow` varchar(50) default NULL,
  `Requestby` varchar(50) default NULL,
  `Authoriseby` varchar(50) default NULL,
  `Withdrawby` varchar(50) default NULL,
  `ImageCount` int(10) default NULL,
  `Return2Lib` varchar(50) default NULL,
  `Return2Drawer` varchar(50) default NULL,
  `Return2Row` varchar(50) default NULL,
  `Returnby` varchar(50) default NULL,
  `Returned` tinyint(1) unsigned NOT NULL default '0',
  KEY `Media ID` (`Media ID`),
  KEY `Withdrawn_No` (`Withdrawn_No`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 8192 kB';

Hope these information helps.

Thanks

I was unable to repeat the behavior reported using Access 2003.

I think the problem is similar to the bug report in 
http://bugs.mysql.com/bug.php?id=12785

I was only able to see the first column if I use the query.
When I try to view the table, I receive the following error message.
could not execute query; could not find linkable table.

Please advise.
Thanks in advance.

Hi 

Can you highlight on which part of the problem you cannot repeat?

Does it mean that you are able to create the view and link it?

Thanks