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


>-----Original Message-----
>From: [email protected] [mailto:[email protected]]
>Sent: Wednesday, January 12, 2011 11:03 AM
>To: Jerry Schwartz
>Cc: 'Lawson K. Cronlund'; [email protected]
>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" <[email protected]>
>To:	"'Lawson K. Cronlund'" <[email protected]>,
>            <[email protected]>
>Date:	2011/01/12 10:01
>Subject:	RE: MS Access/MyODBC Interface Issue
>
>
>
>>-----Original Message-----
>>From: Lawson K. Cronlund [mailto:[email protected]]
>>Sent: Tuesday, January 11, 2011 10:14 PM
>>To: 'Jerry Schwartz'; [email protected]
>>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: [email protected]
>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
>>[email protected]
>>+1(480)308-0641
>>
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:[email protected]]
>>Sent: Monday, January 10, 2011 8:23 AM
>>To: 'Lawson K. Cronlund'; [email protected]
>>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: [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]
>
>
>
>
>
>--
>MySQL ODBC Mailing List
>For list archives: http://lists.mysql.com/myodbc
>To unsubscribe:
>http://lists.mysql.com/[email protected]
>
>





-- 
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/[email protected]