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

[Guest]

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)

 

[Granny Spitz via AccessMonster.com]

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

Try adding a primary key or a unique index to Table b.

 

[Guest]

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

 

[Granny Spitz via AccessMonster.com]

This did not work.

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.

 

[Guest]

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.

 

[Granny Spitz via AccessMonster.com]

Yes, I did all these things. No luck.

Other things to check for:

1) Open the linked view in datasheet view. Right click on the title bar and
choose Unhide Columns. Make sure that all the check boxes are checked.

2) Double check that you're linking to the *right* view in the right MySQL
database. It's easy to click on a similarly spelled, but wrong, name.

3) Make sure that you have the latest MyODBC driver installed on your
computer.

4) I'm running out of ideas. Maybe you could try adding a TimeStamp column
with a default value of CURRENT_TIMESTAMP in tables a and b so that the
linked view will be updateable. (Although this is something that should help
with problem rows, not problem columns.)

 

[Guest]

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

 

[Granny Spitz via AccessMonster.com]

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.

Thanks for mentioning it, but I don't think that's affecting the missing
column.

Have you gotten this to work in a similar situation?

Yes. I didn't have a problem. I don't have MySQL installed on my present
computer, so I can't experiment, trying to find other things that might be
affecting a calculated column.

Hopefully someone else can offer some other suggestions. If you don't get
any more responses on this thread by tomorrow, I suggest you post a new
question so that it goes to the top of the queue and more people will have an
opportunity to see it and offer suggestions. Make sure you include the steps
you've tried so far. Good luck. Sorry I couldn't be of more help.

 

[Guest]

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