You won't like this, but the only way I've solved this problem is with a
Refresh All.

In my case, I have a lot of VB code so I put the refresh in there. I'm going
to look for an alternative, because this is very slow, but at this point I'd
rather have the users wait than get frightened.

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: Thursday, January 13, 2011 4:47 PM
>To: 'Jerry Schwartz'; scaisse@jgh....
>Cc: myodbc@list...
>Subject: RE: MS Access/MyODBC Interface Issue
>
>I'm still looking for probable causes for the problem that I'm experiencing.
>
>The scenario is that when showing a linked view of a MySQL table in MS
>Access I get the following results:
>
>1.  Add an 'x' to the end of a CHAR field in the view.
>2.  Move off the record that's been modified to the adjacent record.
>3.  The record shows in the MS Access window as changed according to the
>change made.
>4.  Move the cursor back to the originally modified record and edit the
>field previously modified to restore it to the original value.
>5.  At this point I get a warning message from MS Access that the record has
>been modified by someone since I modified it and the change cannot be
>applied.
>6.  I click OK in the warning message and again make the change which works
>this time.
>
>This is the simplest description I can come up with from a user's viewpoint.
>
>I have also simplified all the fields in my database to CHAR, DATETIME,
>TIMESTAMP, and INT to try to comply with all the warnings about views and MS
>Access on the web.
>
>My expectation is that when the first change is made and I move from the
>record, that the record would actually be updated in the MySQL data base and
>the change committed and the record available for editing again.
>
>My expectation was that this would be what happened (and does seem to be
>what happens if I work directly with the underlying table instead of through
>the view), but, instead, it seems that the record in the MySQL database is
>still identified as locked or some such status so that when I return to it
>and attempt to edit it again, MySQL returns a locked status and MS Access
>reports that someone else has edited the record since I made my change.
>
>But the locked status is cleared by something MS Access does which restores
>the record status.  Then I can edit the record again, leaving it in the same
>status as before.  It's almost as if MS Access thinks the lock is released
>but the lock is still in place in the MySQL database - if locking is
>involved at all.
>
>This would be fine if I could bury the "retry" inside MS Access but that
>doesn't seem possible in my situation because of the distributed approach
>planned for the users of this database.
>
>I think I'll try MS SQLServer to see if the same problem exists there.  MS
>Access linked to MS SQLServer might be a better integration although it
>doesn't look hopeful from the statements that I see on the web.
>
>If anyone has a suggestion on how I can use the userid approach to limit
>access to specific areas of a table in MySQL and avoid views, I'd be happy
>to experiment with that approach.
>
>There were several suggestions made on this list that gave me research
>approaches and good information and I'd like to thank all of you who
>responded to my plaintive request.  I'm just glad that I'm starting into a
>database development instead of trying to do the much bigger and restricted
>job of migrating/preserving an existing database as some have indicated in
>their posts.
>
>Regards.
>
>
>Lawson Cronlund
>lawson@vrti...
>+1(480)308-0641
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii....]
>Sent: Wednesday, January 12, 2011 11:56 AM
>To: scaisse@jgh....
>Cc: 'Lawson K. Cronlund'; myodbc@list...
>Subject: RE: MS Access/MyODBC Interface Issue
>
>I expect to see the same thing you saw.
>
>Our performance is terrible (except for a few things that went from being
>slow
>to being instantaneous), but I've been concentrating on just getting things
>working. This is a combination sales, product, CRM, and order entry system.
>If
>it doesn't work, neither does anyone in our office.
>
>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: scaisse@jgh.... [mailto:scaisse@jgh....]
>>Sent: Wednesday, January 12, 2011 11:03 AM
>>To: Jerry Schwartz
>>Cc: 'Lawson K. Cronlund'; myodbc@list...
>>Subject: RE: MS Access/MyODBC Interface Issue
>>
>>I suggest also checking the  MySQL Query Log in addition to the ODBC traces
>>(which you can easyly turn on with MySQL WorkBench). For extensive queries,
>>the former will be much faster than the latter.
>>
>>While checking the MySQL Log, I noticed that a lot of my join queries would
>>simply download the entire tables and then do a "local join" on cached
>>items. This, of course, yields to absolutely terrible performance - worst
>>than Native Access tables. What triggers the joins to become "multiple
>>selects", I have no idea, as some queries went through just fine. The
>>performaces went from Native Access on shared drive of ~7s to "download all
>>tables and do local join" of  ~15 seconds to passthrough query of ~0.5
>>seconds...
>>
>>To work around this issue I had to resolve to passthrough Queries, however
>>this means that Access no longer has any references to the indexes so you
>>have to perform updates and deletes with code. Anyway, as mentioned in
>>previous emails, sometimes can't even figure those out on it's own.
>>
>>Through some ODBC trace inspections (though MySQL Query logging could have
>>shown me this too) for normal tables that have the "Record is locked"
>>issue, I found out that for some tables, Access seems to simply ignore the
>>index and tries to do an update statement using a where statement
>>containing the entire row data as conditions!
>>
>>- Sebastien Caisse
>>
>>
>>
>>From: "Jerry Schwartz" <jerry@gii....>
>>To: "'Lawson K. Cronlund'" <lawson@vrti...>,
>>            <myodbc@list...>
>>Date: 2011/01/12 10:01
>>Subject: RE: MS Access/MyODBC Interface Issue
>>
>>
>>
>>>-----Original Message-----
>>>From: Lawson K. Cronlund [mailto:lawson@vrti...]
>>>Sent: Tuesday, January 11, 2011 10:14 PM
>>>To: 'Jerry Schwartz'; myodbc@list...
>>>Subject: RE: MS Access/MyODBC Interface Issue
>>>
>>>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.
>>
>>
>>[JS] That, I believe, is just luck.
>>
>>>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.
>>>
>>[JS] Every table I use has a primary key, and I'm not using views. That
>>being
>>said, many of my forms are based upon queries of one kind or another.
>>
>>>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.
>>>
>>[JS] I'm not sure about that, but now  you have me thinking. In Access, as
>>opposed to MySQL, you can update the result of a SELECT query (or even
>>nested
>>SELECT queries). It must, in some way, be treating that result set as a
>>view.
>>
>>That raises an obvious question: what are the "keys" that Access uses for
>>the
>>result of a multi-table SELECT query? I think this is more of a
>>performance-related issue, though.
>>
>>>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.
>>>
>>[JS] Well, as I said the problem isn't restricted to the use of MySQL
>>views. I
>>still believe that it is related to the local caching and synchronization
>>that
>>has to be done. I can see this in a number of ways, the most obvious of
>>which
>>is that when I insert a record (using a form) the record will show as
>>#DELETED
>>until I refresh.
>>
>>Nice use of the dieresis, by the way.
>>
>>As time permits, I'm going to be running some ODBC traces so perhaps that
>>will
>>shed light on what's going on. I really need to put some speed into this
>>application, because it can take almost a minute for some of my forms to
>>refresh.
>>
>>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
>>
>>
>>
>>
>>>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=scaisse@ts.j...
>>
>>
>
>
>
>
>
>--
>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...