Jerry,

Thanks for the reply.

I look at this problem slightly differently.  I've determined that the
problem doesn't occur when you deal directly with the underlying table.
This is presumably because MS Access is formally aware of the primary key
since that's the only difference.

So, if MySQL is able to apply underlying indices to the view (at least for
the primary key) this wouldn't happen.

And, if MS Access could interpret the selection of the primary index it's
given during the linking of the table as a primary key, this wouldn't
happen.

I'm sure that I'm speaking from a naïve viewpoint since I don't have a
thorough understanding of database technology but I can tell that the
presence of the primary key index in the underlying table would resolve this
problem if it could somehow be inherited by the view.

Just to be clear, the VIEW in this case is strictly a SELECT * FROM a single
table that has a primary key.

Regards,


Lawson Cronlund
lawson@vrti...
+1(480)308-0641

-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii....]
Sent: Monday, January 10, 2011 8:23 AM
To: 'Lawson K. Cronlund'; myodbc@list...
Subject: RE: MS Access/MyODBC Interface Issue

You've come to the right place.

Unfortunately, your problem is not related to (or, at least, restricted to)
using views. That's a red herring. If you look around the web you'll see
that
this is a common problem with no clear solution. Some of the suggestions
I've
seen verge on magical thinking.

I've been working for two weeks to clear this up, and to get rid of the even

more frightening error 3197. After much horsing around with my application
design, I decided to return to one of my first principles: there is no point

in arguing with the computer. The computer might not be "right," but it
isn't
going to change its mind.

One thing I have come to understand, although I can't say that it leads to a

real solution, is that it is very easy to modify data without thinking about

what is really going on. Here are some things I've concluded:

-  Unless you are using pass-through queries, you are generally working on a

local cache of the data.
-  It doesn't necessarily mean that someone else edited the data. You,
yourself, are editing data. That dirties the cache, and Access gets
justifiably concerned.
-  For example, if you insert (append, in Access terms) a record you will
often not be able to find that record by scrolling back and forth.
-  So far as I know your data is always saved even after getting one of the
errors. It scares the heck out of the users, of course.

I don't know if I'm on the right track or not, but

-  I added a timestamp field to every table that I edit. This is recommended

all over the web, although it doesn't seem to be a universal solution.
-  I have larded my code with .Refresh. In my application, that is causing
performance problem; but my immediate concern is to get the application
working.
-  Sometimes I use a pass-through query instead of working with a recordset.

They are harder to maintain, but I'm used to it from using other things like

PHP.
-  In those cases where I have two forms working on overlapping sets of
data,
I update the other form rather than updating the underlying data.
-  If at all possible, test in a multi-user environment. I got some nasty
surprises moving from my test environment to production.

As I said, I don't know for sure that these techniques will always, or even
often, work; but that's where I am.

I hope it helps.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii....
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Lawson K. Cronlund [mailto:lawson@vrti...]
>Sent: Monday, January 10, 2011 1:45 AM
>To: myodbc@list...
>Subject: 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
>
>lawson@vrti...
>
>+1(480)308-0641
>
>+1(602)996-0376 (fax)
>
>Voice Response Technologies, Inc.
>
>5717 E. Justine Rd.
>
>Scottsdale, AZ 85254
>
>





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=lawson@vrti...


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=subscriber@open...