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" <[email protected]>
To:	"'Jerry Schwartz'" <[email protected]>, <[email protected]>
Cc:	<[email protected]>
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
[email protected]
+1(480)308-0641


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