RE: MS Access/MyODBC Interface Issue

Actually, based on the link that I posted the other day it looks like many of 
the same problems (and possibly solutions) apply to MS SQL Server.

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: Friday, January 14, 2011 10:43 AM
>To: Lawson K. Cronlund
>Cc: 'Jerry Schwartz'; [email protected]
>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" <[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]