Description:
Hi
I have raised a problem, but you just reply can't repeat and stop helping.
Please can you give me more advice than that.
Thanks
How to repeat:
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
[30 Oct 10:19] Vasily Kishkin
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 ?
[31 Oct 10:28] [ name withheld ]
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
[31 Oct 20:10] Miguel Solorzano
I was unable to repeat the behavior reported using Access 2003.
[1 Nov 0:46] [ name withheld ]
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.
[2 Nov 14:19] [ name withheld ]
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
