MySQL Bugs: #12578: linked 5.0.11-views fail with Access 97 SR2

Bug #12578	linked 5.0.11-views fail with Access 97 SR2
Submitted:	15 Aug 2005 8:50	Modified:	31 Aug 2005 16:45
Reporter:	gernot adams	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.11-2	OS:	Microsoft Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
hi,
i create a view with MySQL 5.0.11 and link this view in MS Access 97 SR2. When i open this view i get the error message:

Could not Execute Query; Could not find linked table.

When i click on OK i get a second error message that says:

Table 'im0344.tblex' doesn't exist (#1146).

When i open the system table "MSysObjects" in Access i see in the column "Foreign Name" that the entries in this column doesn't regard to the entries in the column "Name". For example: in MySQL 5.0.10 i have a view called "viewactivemonitors". And so the entries in the columns "Foreign Name" and "Name" are the same: "viewactivemonitors". Under 5.0.11 in the "Name"-column the entry is "viewactiveinvestigators" and in the column "Foreign Name" the entry is just an "a".

This Problem doesn't happen under MySQL prior to 5.0.11 and it doesn't happen under Access 2003.

Do you have an idea about that?

cheers
gernot

How to repeat:
see above

I tested with Access 2003 without to repeat the behavior
reported (like you have mentioned). Then I just to recommend
you for to apply the latest service packages for Access 97
and upgrade your MDAC for the last one.

hi,
i have already had installed the latest Access 97-SR (which is SR2b). Now i have installed MDAC 2.8 SP1 too but the problem still remains.

To me the problem is that MS Access 97 don't get the right information about the view's name. When Access links tables or views, it stores a local name and a foreign name for the object in the Access system table "MSysObjects" (this table is hidden so go to tools -> options -> view, and select "hidden objects" and "system objects"). For example in the table-window in MS Access you can rename a linked ODBC-table or view to whatever name you wish. This name will be stored in the "Name" column. And the "real" name which is the name of the view or table in MySQL is stored in the "ForeignName"-column. And under Access 97 a "wrong" name will be stored for views and then Access can not find the view.

cheers
gernot

Did you deleted the linked view and relinked after the update ?
Notice that Access saves the string connection so to delete and
relink solves the problem.

Also are you using the latest MyODBC ?

hi,
i use the latest MyODBC driver (MyODBC-3.51.11-2). After i had installed the latest MDAC (2.8 SP1) i relinked the view. And i also created a new view (one like SELECT * FROM tblBla) but the problem still remains. And i tested it with several Access 97-clients. All clients have Windows 2000 SP4 with the latest MS Updates. The Server is Windows 2003 SP1 with the latest MS Updates.

cheers
gernot

Sorry..I tryed to reproduce the bug from MS Access 2003. I have installed MDAC 2.80 on Windows 2003. Could you please test it on MS Access 2003 ?

hi,
as i stated in my first posting: this problem doesn't occur under MS Access 2003 but only under MS Access 97.

cheers
Gernot

I was unable to reporduce this behaviour.

hi Jorge,
under what kind of configuration do you tested it?

cheers
gernot

hi,
the problem still remains under MySQL 5.0.12

cheers
gernot