why are fields missing when linking a table to a mysql view

[Peter Kopke]

When I create a Linked Table over a MySQL 5.0 view through ODBC,
some of the fields do not appear in Access. Here is a
simple example:

create table a (
the_key int PRIMARY KEY
);

create table b (
the_key int,
entry int
);

create view c as
SELECT a.the_key, sum(b.entry) as total
FROM a left outer join b on (a.the_key = b.the_key)
GROUP BY a.the_key;

When I create a linked table in Access oon the view c
it only has one field! The total field is ignored.

Can anyone help with this?

Thanks,
Peter Kopke
(e-mail address removed)

 

[Peter Kopke]

This did not work. I deleted the view and the tables in
Access and MySQL. I recreated table a, and changed
the definition of b to:

create table b (
the_key int,
entry int,
primary key (the_key, entry)
);

I recreated the view, and recreated the table
over view c in Access. Same thing, the total
field is not present in Access.

Regards,
Peter Kopke

 

[Peter Kopke]

Yes, I did all these things. No luck.

Peter

Did you drop the link and recreate it after the change? The table's metadata
(column names and sizes, statistics, database password, etc.) is stored in
the Access database at the time of link creation and is *never* updated. If
you make structural changes to the linked table or replace it entirely, you
have to drop the link and recreate it to pick up the *current* table metadata.
Refreshing the link won't work. You need to:

1) Drop the link to the view.
2) Drop the view.
3) Drop table b.
4) Recreate table b with the primary key.
5) Recreate view c.
6) Recreate the link to view c.

 

[Peter Kopke]

When I choose Unhide columns only the one column is
available.

Something I did not mention before, I am using readonly
access to the database. I.e., the use specified in the
ODBC connection has only read-only access to the
tables.

Have you gotten this to work in a similar situation?
It seems to me that perhaps MySQL is treating "derived"
fields differently from normal fields.

Regards,
Peter Kopke

 

[Peter Kopke]

Thank you for your suggestions. I have been able to
see the MySQL view properly through ODBC using a different
front-end. So it appears to be an Access issue.

Regards,
Peter Kopke