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.

Regards.


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


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