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]