MySQL Bugs: #11017: linked Views in MS Access are not updateable

Bug #11017	linked Views in MS Access are not updateable
Submitted:	1 Jun 2005 13:14	Modified:	21 Oct 2005 20:14
Reporter:	gernot adams	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.4 and 5.0.6	OS:	Windows (Windows)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
hi,
with MySQL 5.0.3 i created views (just simple ones like "select * from table bla" - without any JOINS; the tables on which the views are based all have a Primary Key integer-column and a Timestamp column). I linked those views via MyODBC (v.3.51.10-2 and v3.51.11-2) in MS Access 97 SR2b and Access 2003 SP1. Everything works fine. The MySQL-Server in running on Windows 2000 Server SP4. The client is Windows 2000 SP4.
When i craete those views in MySQL 5.0.4 or 5.0.6 MS Access didn't recognize the Primary Key column and so the views are not updateable. When i try to link a view MS Access opens a wizard and asks to select the Unique Identifier. And even when i select the correct Unique Identifier (the primary key column) MS Access still handle this views as not-updateable. When i open the view in MS Access in the design modus MS Access add an index named "__uniqueindex" on the column i select in the wizard. 
In MySQL 5.0.3 this didn't happen. When i open the view in MS Access in the design modus i see that MS Access automatically add an index named "PRIMARY" on the primary key column. 

Do you have any idea about that?

cheers
Gernot

How to repeat:
With MySQL 5.0.4 or 5.0.6 create a view based on a single table. Then try to link this view in MS Access.

I was unable to reproduce the behavior reported with simples
table -> views I did. Could you please provide a table and view
definition that on your side fails.

Thanks in advance.

hi Miguel,
thank you for your fast response. Here is a table and view definition. And my other views and tables are the same simple ones like that:

#tblTrialCentre
#=========

CREATE TABLE tblTrialCentre 
(
TrialCentreID		SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
TrialCentreNo		VARCHAR(6) NOT NULL,
TrialCentreStatusID	TINYINT UNSIGNED,
FirstPatientIn		DATE,
DateOfContract		DATE,
Notes			VARCHAR(255),
Timestamp		TIMESTAMP,
oldTrialCentreID	SMALLINT UNSIGNED,

PRIMARY KEY (TrialCentreID),
INDEX TrialCentreStatusID (TrialCentreStatusID)
);

#viewTrialCentre
#===============
CREATE VIEW viewTrialCentre
AS
SELECT
   *
FROM
   tblTrialCentre
;

greetings 
gernot

Hi,

I wasn't able to reproduce this bug using MyODBC 3.51.11-2, MySQL 5.0.4 & MS Access 2003

What I found with your test case is the known issue #Deleted and
the view is updated but you should see the new rows after closing
and re-open the view.
Also the table itself behaves in the same way as the view.
However I still need to test with new MDAC stuff.

Thank you for the feedback.

hi,
i did the following steps to check the problem once more:
i newly installed the server-pc with Windows 2000 Pro SP 4 and all MS-Updates. Then i installed MySQL server 5.0.6. and newly created my database. I newly installed a client with Windows 2000 Pro and all MS-Updates. On this client i installed MS Access 97 SR2b. I connect to the server via MyODBC-3.51.11-2.

When i link a view MS Access still doesn't recognize the primary key column and again a wizard comes up where i have to select the pk-column. So i select the primary key column but then the view is UPDATEABLE. So unlike to my yesterday situation i now have at least partial success.

Then i deinstalled MySQL 5.0.6, delete the database, installed MySQL 5.0.3. and created the same database again. Then i connect from MS Access, link the same view and voilà: MS Access recognize the primary key column and also all other columns with an index (one can see it if you open a view or table in MS Access in design mode and then click on "Index" With a MySQL 5.0.3-database all indexed columns of the linked tables and views are listet correctly. But with 
the MySQL 5.0.6-database MS Access list only the column with an index that i select in the wizard (see above).

So i don't know what it is but something has changed in the behavior after Version 5.0.3.

Only assigned temporarily to determine cause. This _looks_ most likely like a server issue since the only variable in the testcase is server version.

hi,
i the meantime i experienced the problem still exists in Version 5.0.7 and 5.0.9. But i have some further information about how MS Access 97 treats SQL server views.

In the MS book "Building Applications with Microsoft Access 97" in Chapter 19 "Developing Client/Server Applications" i found the following on page 4:

"If your server supports SQL views, you can link them to your local database. ... Microsoft Access treats a linked view exactly like a linked table without indexes."

and further on page 6:

"If your server allows you to update data by using SQL views and you want to take advantage of this updatability in Microsoft Access, you need to create a pseudo index on the linked view. This index tells Microsoft Access the field or fields that make up the primary key for a record returned by the view—the field or fields that uniquely identify the record. Microsoft Access can then create an updatable dynaset on the view, which forms and queries can use to view and update data.
For example, suppose you link an SQL view named SeptOrdersView that returns
a subset of records in the remote Orders table, and you name the linked view SeptemberOrders. Because the OrderID field is still unique within the view, you would use a data-definition query to create the index by running the following
SQL statement:

CREATE UNIQUE INDEX index1 ON SeptemberOrders (OrderID)

This doesn’t create a real index on the server or in your local database, nor does it take up much space locally. It simply tells Microsoft Access that the OrderID field is the primary key for the linked view."

The creation of the index is done by the MS Access wizard. When i try to link a view the wizzard comes up and ask for the primary key column. When i select the pk-column it creates this index like shown in the example above. And after creating this index the view is updateable.

cheers
gernot

I'm assuming that after following the directions you've posted, that you are claiming that this bug is no longer an issue?