MS Access/MyODBC Interface Issue

If this should be posted to another forum, please let me know.  I'm posting
it here because of the interaction with MyODBC/MySQL and the apparent
difference between a view and a table in primary key identification.

 

I am experiencing a peculiar problem and have not been able to find a
description/solution that matches what I'm seeing.

 

The problem:

I have a MS Access 2007 being used as a front end to a MySQL database view
using ODBC.

 

MySQL:                        MySQL 5.1.49-1ubuintu8.1

ODBC               MySQL ODBC 5.1 Driver - Version 5.01.08.00

MS Access       2007 - most recent patches as of 1/9/2011

 

The MySQL database has one main table, some supporting table for foreign key
validation/control of some column constraints and a number of views that are
used to limit the visibility via userid/password login to the main table.

 

The main table has a primary key defined (int not bigint) and a timestamp
field.  All time associated fields are defined as datetime.  There are no
bigint columns.

 

Each of the normal userids is provided privileges to only one of the views.

 

When logged in to the database via MS Access using one of the single view
only userids, I can make a change to a simple varchar (not constrained)
field in a row, move off the row successfully, and verify that the change
was made in the MySQL data table.

 

However, if I do the same thing (in MS Access) up to the point of moving off
the changed row (presumably committing the change) , and then return to the
same row and try to undo the change, I get an error from MS Access saying
"The data has been changed  --  Another user edited this record and saved
the changes before you attempted to save your changes.  Re-edit the record".
If I click  OK and then go to the row in question again, the second time the
change can be made.

 

Obviously, having an error come up for simple editing is not a good thing.

 

This does not happen when accessing the main table directly as a table (not
through a view).  When I set up the linked tables in Access it identifies
the primary key in the table but needs me to manually specify the primary
key in the view.  It asks for a column that it can use as a unique
identifier and lists the fields when I choose the objects to link to.

 

Is the primary key not identified in a vie or is there something I should do
to identify it?

 

Thanks in advance for any help you can offer.

 

 

Lawson Cronlund

[email protected]

+1(480)308-0641

+1(602)996-0376 (fax)

Voice Response Technologies, Inc.

5717 E. Justine Rd.

Scottsdale, AZ 85254