Shawn,

My thanks to you for your professional description of the root cause of this
problem.

My view is only from the orbit so I don't have the knowledge that people "in
the trenches" have.

Perhaps, someday, this will get resolved.  In the meantime, I'll have to let
my customer know that MS Access is not a valid client to use with MySQL (and
probably not with any other ODBC based client).

My best guess now is that MS Access is probably the database program that
would best provide a reliable server side database for MS Access to link to
from the client side.  But the features of MS Access prevent me from
considering that a viable course to recommend - scaling, security model,
efficiency and others that I probably don't even know about.

Thanks again for providing clarity on a murky issue.  If MySQL ODBC finds a
way to provide a user level solution/bandaid to this I'll be happy to go
back to my original approach.

Regards,


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


-----Original Message-----
From: Shawn Green (MySQL) [mailto:shawn.l.green@orac...]
Sent: Friday, January 14, 2011 12:38 PM
To: Lawson K. Cronlund
Cc: myodbc@list...
Subject: Re: MS Access/MyODBC Interface Issue

On 1/14/2011 14:04, Lawson K. Cronlund wrote:
> Jerry/Sebastian,
>
> In looking up MS Access linking to MS SQL tables via ODBC, I also found
that
> I should expect the same problems.  However, I'll proceed on the
assumption
> that the MS Access/MSSQL/ODBC developers have resolved the problem or it's
> simply unresolvable.  I suspect that's the case because there are some
very
> good developers in the MySQL ODBC community and they seem to have run into
a
> problem that is complex enough or conflicted enough that there is no easy
> solution.
>
> The MS SQL information that I read seems to indicate that a problem like
> this might arise because of the attempts to speed up the presentation of a
> recordset to the screen form.  I don't know how valid the information that
I
> read is, but it describes how the MS SQL ODBC driver fetches 10 rows at a
> time and doesn't fill the whole recordset expect as a long term slow task.
> It tries to anticipate the next page that an end user looks at.  Updating
> the recordset becomes complex.  If the MySQL ODBC driver engages in the
same
> kind of antics for the sake of speed, that might be the cause of the
> problem.
>
> The impression I've gotten is that the problem arose in one of the steps
in
> the MySQL ODBC 3.51 line of development - I think around the .17 or .18
> version.
>
> I use Navicat as my administrative interface to MySQL and I've noticed
that
> it does not have the problem that we are experiencing with the ODBC
> interface.  Obviously, it doesn't use ODBC to connect to MySQL and
translate
> SQL statements from an application into SQL that is supposed to be better.
>
> In my situation, I've also planned a web interface for the users of the
> database I'm putting together.  This is a PHP5 interface (LAMP).  Since it
> operates with direct SQL to the MySQL database, I don't expect the
problem.
> However, I don't want to abandon the more sophisticated users who would
use
> the database records (membership in a non-profit) as part of their
affiliate
> unique processing.
>
> I'll switch my development priority to the web portion and hope that the
> MySQL ODBC developers who read this list may be able to find a way around
> the problem.
>
> I'd be happy to use an alternate approach to this if I could think of one.
> If there is a way to achieve the goal of limiting access to portions of a
> medium size database table based on userid I'd like to know about it.
>
> Regards,
>
>
> Lawson Cronlund
> lawson@vrti...
> +1(480)308-0641
>
>
> -----Original Message-----
> From: scaisse@jgh.... [mailto:scaisse@jgh....]
> Sent: Friday, January 14, 2011 8:43 AM
> To: Lawson K. Cronlund
> Cc: 'Jerry Schwartz'; myodbc@list...
> Subject: RE: MS Access/MyODBC Interface Issue
>
> I'm pretty sure that the locking mechanism issues in Access have nothing
> with MySQL Server but rather either Access, ODBC or the MySQL ODBC Driver.
> Simple case of point, if you have a record "locked" under Access, you can
> still update it through a direct MySQL query (I usually use MySQL Query
> Browser though MySQL WorkBench does the trick too).
>
> Chances are that MS SQL will not have these issue since the driver is
> diffenrent and I'm expecting Microsoft has less issues interfacing with
> their own stuff then (now) Oracle... However I've never tested MS SQL for
> various reasons and I can't say for sure.
>
> - Sebastien Caisse
>
>
>
> From: "Lawson K. Cronlund"<lawson@vrti...>
> To: "'Jerry Schwartz'"<jerry@gii....>,<scaisse@jgh....>
> Cc: <myodbc@list...>
> Date: 2011/01/13 16:47
> 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.
>

A big part of the problem is with MS Access itself. How it determines if
a record has changed between access and update is still not public
knowledge. Therefore, database access provider developers (like the guys
that write ODBC drivers) need to guess at what it wants to know.

Our best guesses involve a combination of primary keys and timestamp
columns. But honestly, the problem isn't really with MS SQL or with
MySQL as much as it is that neither product tracks or reports row
changes exactly the way that Access wants to see them. Access is not
written with client-server data access patterns in mind. It was written
to share or have exclusive control over a proprietary on-disk,
file-based storage format and that isn't always compatible with how
other databases operate.

The most stable solutions involve detaching the automated data storage
and retrieval actions from the GUI data presentation. The native
recordsets may need to be replaced by SQL-based recordsets using
pass-through queries just as any other data driven GUI needs to be
(think web development models, here). Navigation buttons may need to be
rewritten to apply any changes that were made to the data so that they
use explicit SQL statements instead of the native Access automation.

The less complicated you try to be, the better your odds will be of
making it work.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
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...