MySQL Bugs: #14611: VIEWS can't be linked correctly to MS ACCESS 200*

Bug #14611	VIEWS can't be linked correctly to MS ACCESS 200*
Submitted:	3 Nov 2005 16:14	Modified:	6 Jul 2007 3:00
Reporter:	Antonio Leal Elizondo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.13	OS:	Windows (windows)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	ODBC5-RC

Description:
Hello. I used to have mysql 5.09. and when i liked my VIEWS ( exactly the same way i liked tables ) it was GREAT.

 But when i update to 5.13 / 5.15 , the VIEWS having more that 1 table DOESn't WORK, it says that The address can't be located, i check that oue, and found that teh problem is the ALIAS in the FIELDS and the JOIN with other tables... THING that was OK in previews version like 5.0.9.

How to repeat:
-

Suggested fix:
-

Thank you for a problem report. 

Please, provide the definition of tables and view that does not work for you any more. It may be related to the change in join behaviour starting from 5.0.12...

Definitions and Views

Attachment: thoganoviembre.sql (text/plain), 51.84 KiB.

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".

Hello... i need to know if a new version will be released with the bug fixed.. talking about the VIEWS linked to MS Access... due to the fact that, those don't work property.... Thanks for all.

Please, inform what does not work for you exactly. I was able to create all tables and views from your file (http://bugs.mysql.com/file.php?id=2278) on 5.0.15-nt:

...
mysql> #
mysql> # Definition for the `v_solicitudescredito` view :
mysql> #
mysql>
mysql> DROP VIEW `v_solicitudescredito`;
ERROR 1051 (42S02): Unknown table 'thoga.v_solicitudescredito'
mysql>
mysql> CREATE VIEW `v_solicitudescredito` AS
    ->   select
    ->     sql_no_cache `s`.`idSolicitudesCredito` AS `idSolicitudesCredito`,
    ->     `s`.`clave_empleado` AS `clave_empleado`,
    ->     `s`.`clave_cliente` AS `clave_cliente`,
    ->     `s`.`FolioPedido` AS `FolioPedido`,
    ->     `s`.`clave_art` AS `clave_art`,
    ->     `s`.`fechaIngreso` AS `fechaIngreso`,
    ->     `s`.`fechaAutorizacion` AS `fechaAutorizacion`,
    ->     `s`.`FolioContrato` AS `FolioContrato`,
    ->     `s`.`ClaveCataEstatus` AS `ClaveCataEstatus`,
    ->     `s`.`ClaveCataEstatusPago` AS `ClaveCataEstatusPago`,
    ->     `s`.`FechaPagoComision` AS `FechaPagoComision`,
    ->     `s`.`clave_empFirmas` AS `clave_empFirmas`,
    ->     `s`.`FechaLimite` AS `FechaLimite`,
    ->     month(`s`.`fechaIngreso`) AS `elmes`,
    ->     if(((`s`.`ClaveCataEstatus` <> 1) and (ifnull((to_days(`s`.`FechaLimi
te`) - to_days(curdate())),
    ->     0) < 0)),
    ->     abs(ifnull((to_days(`s`.`FechaLimite`) - to_days(curdate())),
    ->     0)),
    ->     0) AS `dias`,
    ->     `s`.`Observaciones` AS `Observaciones`,
    ->     `s`.`id_grupo` AS `id_grupo`,
    ->     `s`.`stamp` AS `stamp`
    ->   from
    ->     `tbl_solicitudescredito` `S`
    ->   order by
    ->     `s`.`fechaIngreso`;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> show tables
    -> ;
+------------------------------+
| Tables_in_thoga              |
+------------------------------+
| tbl__menu                    |
| tbl__pagos                   |
| tbl__seguridadusuarios       |
| tbl__tiposclientes           |
| tbl__usuarioaccesos          |
| tbl_articulos                |
| tbl_articulosbranch          |
| tbl_articulosprecios         |
| tbl_branches                 |
| tbl_cataestatus              |
| tbl_catalogocontratos        |
| tbl_catalogodepartamentos    |
| tbl_catalogopuestos          |
| tbl_categoria                |
| tbl_clientes                 |
| tbl_comisionan               |
| tbl_empleados                |
| tbl_facturassolicitudes      |
| tbl_grupos                   |
| tbl_reportes                 |
| tbl_reportesestadisticos     |
| tbl_seguridadnivelesusuarios |
| tbl_solicitudescredito       |
| tbl_solicitudesdecontar      |
| tbl_tablaasesoresbonos       |
| tbl_tablasupervisor          |
| tbl_tablasupervisorbonos     |
| tbl_tipoarticulos            |
| v_empleados                  |
| v_imprimefactsolicitudes     |
| v_rpt_comisiones             |
| v_solicitudesadescontar      |
| v_solicitudescredito         |
+------------------------------+
33 rows in set (0.03 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.00 sec)

What shell I do next?

hi,
i faced the same problem and i've also heard it from others. This problem exists since MySQL version 5.0.11 and up to Version 5.0.16. Version 5.0.10a was the last one were this problem doesn't occur.

Simply create a MySQL-view having more than one table and then link this view in MS Access (File -> Get external Data -> Link Tables). Then open this view in Access. Then you get the error message:

Could not Execute Query; Could not find linked table.

The problem is with the alias-name and the MyODBC-driver. 

Here's a workaround:

CREATE VIEW xyz AS
SELECT 
    ...
FROM
    test AS t
LEFT JOIN
    other o
ON t.id = o.otherid

=> can't open view

CREATE VIEW xyz AS
SELECT 
    ...
FROM
    test AS xyz
LEFT JOIN
    other o
ON xyz.id = o.otherid

=> can open view

cheers
gernot

Dear.. Valeriy Kravchuk.
  You are right the SQL script i posted works perfectly, the problem is when i link the VIEWS having more than one table in MS ACCESS. (File -> Get external Data -> Link Tables). Then 
open this view in Access. Then you get the error message: 

Could not Execute Query; Could not find linked table.

 As people say it happens since 5.0.11. because it was working fine in older versions..

Thanks for all. Expecting the bug fixed.

So, the problem may be related not to MySQL server itself, but to the ODBC driver (MyODBC). So, please, inform about the exact version of MyODBC used.

Can you switch on tracing for the ODBC data source while trying to link view in Access and upload the trace file here?

Hello,

I have the same problem with views in MSAccess 2003 through ODBC.
However, there's no error message reported, but some columns of the views
that I've created seems to be missing. 

Here's the table definitions involved :

-- --------------------------------------------------------

-- 
-- Table structure for table `saldtl`
-- 

CREATE TABLE saldtl (
  REFNO varchar(12) default NULL,
  STONUM varchar(13) default NULL,
  `DESC` varchar(25) default NULL,
  PRICE decimal(12,2) default NULL,
  COST decimal(12,2) default NULL,
  SUPP varchar(10) default NULL,
  QTY decimal(10,3) default NULL,
  UNIT varchar(6) default NULL,
  UCONV decimal(6,0) default NULL,
  AMOUNT decimal(16,3) default NULL,
  OLDPRICE decimal(12,2) default NULL,
  DISC_DESC varchar(20) default NULL,
  DAMT decimal(10,3) default NULL,
  FREEQTY decimal(10,3) default NULL,
  REMARK varchar(30) default NULL,
  STOCKED varchar(1) NOT NULL default 'N',
  KEY REFNO (REFNO),
  KEY STONUM (STONUM),
  KEY PRICE (PRICE),
  KEY STOCKED (STOCKED)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

-- 
-- Table structure for table `salhfl`
-- 

CREATE TABLE salhfl (
  REFNO varchar(12) NOT NULL default '',
  `DATE` date default NULL,
  LOC char(3) default NULL,
  AMOUNT decimal(16,3) default NULL,
  SALLOC char(3) default NULL,
  YOURREFNO varchar(12) default NULL,
  TERM decimal(3,0) default NULL,
  DISC_DESC varchar(20) default NULL,
  SALESMAN char(3) default NULL,
  SOREFNO varchar(12) default NULL,
  ONLINEPOST char(1) default NULL,
  REMARK varchar(30) default NULL,
  REMARK2 varchar(30) default NULL,
  REMARK3 varchar(30) default NULL,
  REMARK4 varchar(30) default NULL,
  REMARK5 varchar(30) default NULL,
  BIAYA decimal(10,2) default NULL,
  B_DESC varchar(30) default NULL,
  PRIMARY KEY  (REFNO),
  KEY `DATE` (`DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here's the views :

	CREATE VIEW r1a_v_wksofar_agg_sale_by_dept as 
	SELECT 
	SALDTL.STONUM AS STONUM,
	`DESC` AS 'DESC',
	LEFT(SALDTL.STONUM,3) AS DEPT,
	sum(SALDTL.QTY) AS QTY,
	CAST(sUM(SALDTL.AMOUNT) AS SIGNED) AS AMOUNT
	FROM SALDTL LEFT JOIN SALHFL ON SALDTL.REFNO = SALHFL.REFNO
	WHERE SALHFL.DATE BETWEEN CAST( CURRENT_DATE() - DAYOFWEEK(CURRENT_DATE())  + 1 AS DATE)
	AND  CURRENT_DATE()
	GROUP BY  SALDTL.STONUM
	ORDER BY DEPT,`DESC`;

When I link the view using MSAccess 2003. I've got only 2 column showing
STONUM, DESC.

I'm using MYSql Server : 5.0.15.
ODBC Connector : 3.51.12
MSAccess : 2003.

The driver used is MyODBC-3.51.11-2-win...

  thanks alot...

hi,
the problem also occurs with ODBC-Driver 3.51.12

gernot

I was able to reproduce the bug on Access 2003 and mysql 5.0.16. I used myodbc 3.51.12

Hello...
   Yeah the problem persists since mysql 5.0.12 and ODBC 3.51.12.
   Using mysqk 5.0.9 and ODBC 3.51.12 there was no problem at all, with the views that are linked to ACCESS 2003.
  
   We all expect to have this bug fixed... because we prefer mysql instead of others.... 
THANKS for all

Hi,

Same problem here:

- Create view JOINing tables, and/or views from views
- Link the view from MS Access 200*
- There will be missing columns. My guess (not 100% positive on this) is that only the fields from the first available table, table will be shown. 
- Fact: 

MySQL 5.0.18, ODBC 3.51.12

OTOH, linking with the same ODBC driver from within an Excel works without problems.

This bug is a duplicate of http://bugs.mysql.com/12785.  That thread offers a user-submitted patch, but it has not yet been verified.

Yeah those Threads are the same, in the other one it seems to have a good PATH, but we need that path in the newers versions...

  Let see if the MySQL developers fix it soon, becasue it is a problem that needs to be fixed as soon as posible.. 
ALEAL

Experience the same bug with
MySQL 5.0.18
MyODBC 3.51.12
Access 97

There are only 3 columns show up when I use
File >> Get External Data >> Link Tables >> ODBC
The View has the problem linked in Access is a simple LEFT JOIN between two tables.

The work around provided by gernot adams does work around! Thanks.

Hi,
I have an similar problem, but related with permissions.
I have an "GRANT select ..." only on views. My SQL Server only have see the granted views, but with access, i can view and read all tabes. The tests where done on the same machine and rechecked the permissions.
I have MySQL-server-5.0.18-0 and 3.51.12 odbc driver.

Regards, 
Nuno Palma

Hello.. I'm still waiting for a solution of that problem... i'm still using mysql 5.09 to have that problem fixed, but it limits the power of newer versions, i  mean i want to use newer versions but i can't because the problem is still happend...
 I'll really like to know if it has any fix.

 thanks

It is the problem in the client library. See the bug #19671 mysql_list_fields returns incorrect table name for VIEWs.
It causes the wrong interpretation of VIEWs with the alias.
As a temporary workaround I'd suggest not to use aliases instead of the table names.

finally a patch was made... go to http://forums.mysql.com/read.php?65,52721,78517#msg-78517

and download the connector ODBC fixed =)
i hope u to enjoy it.

Does anyone have a copy of the connector fix for this.  The link listed here is dead and I could really do with getting views linked into MS Access asap.

Do you want me to email you it?

Thanks Jeremy.  That would be most useful, though it might be an idea for it to be publicly available until such time as this fix goes into the regular release.

You can e-mail it to larry at senior dot co dot uk.

hi, can some of You send me an email with correct link to this connector ?
styx at punkt dot pl

Hello here is the link to download the fix. it is an Unzip executable, you must click on unzip to install the path, it is copied in c:\windows\system32

http://www.univillarica.mx/updates/segundo.exe

Antonio Leal.

It was the server's issue as MySQL Server 5.0 haven returned the alias instead of the name of appropriate VIEW. MySQL Server 5.0.26 has this bug fixed. I would not recommend using patches of MyODBC such as:

Index: catalog.c
===================================================================
--- catalog.c	(revision 137)
+++ catalog.c	(working copy)
@@ -774,7 +774,7 @@
         row[1]= "";         /* No owner */
 
         /* TABLE_NAME */
-        row[2]= curField->table;
+        row[2]= curField->org_table;
 
         /* COLUMN_NAME */
         row[3]= curField->name;

This can lead to the security issues as using VIEWs is one of protective layers that hide true table names from possible harmful actions.
MyODBC 3.51.12 can be used for linking VIEWs from Access without any patches on the client side.
The bug report http://bugs.mysql.com/bug.php?id=19671 caused current bug has been fixed.

Is this bug now fixed in 5.1? If so, what is the version number?
5.1.15? Thanks.

I wonder if there is any progress on this problem.

I reproduced the problem using MySQL 5.0.32 running on debian linux and MyODBC 3.51.16 (also with .12) on Windows XP.

When creating a view that joins two tables, ODBC shows only the fields of the first table.
With the workaround from gernot adams all fields are shown.

Example:
create table a (i int, v varchar(50));
create table b (i int, w varchar(50));

create view v_test as select a.*,b.w from a join b on (a.i=b.i);
-> ODBC only shows the fields of table 'a'.

create view v_test as select v_test.*,b.w from a v_test join b on (v_test.i=b.i);
-> ODBC shows all fields.

Hello this Bug is supposed to be fixed..
I'm using 5.0.37-community-nt Windows and mysqlodbc-3.51.14-win32.msi

I used to have the same problem BUT now I don't have any trouble trying to link views to MSAccess...

however i have a pack of ODBC files that fix the problem ( it was necesary before not now )... if u are still having the problem, email me and i can send it to u ... 
aleal@univillarica.mx

thank you Antonio. 

The problem seem to have been fixed in some version > 5.0.32. I installed the latest version 5.0.41 now (is not part of the official debian 'stable' packages) and the problem is gone.

Fixed in 5.1.12 as noted on bug#19671

Bug#19671 was fixed in 5.0.25 and 5.1.12.