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: [email protected]
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Lawson K. Cronlund [mailto:[email protected]]
>Sent: Monday, January 10, 2011 1:45 AM
>To: [email protected]
>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
>
>[email protected]
>
>+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/[email protected]