RE: MS Access/MyODBC Interface Issue

>-----Original Message-----
>From: Lawson K. Cronlund [mailto:[email protected]]
>Sent: Friday, January 14, 2011 2:05 PM
>To: [email protected]
>Subject: RE: MS Access/MyODBC Interface Issue
>
>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.
>
[JS]  I believe it to be irresolvable at the ODBC level. You'd have to be able 
to put Access into a non-cached mode. Access is optimized for efficient 
navigation around a record set, and turning off caching would make it much 
less efficient. Imagine trying to use Find on a non-cached record set.

>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
>
[JS] That is my impression as well.

>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.
>
[JS] MySQL ODBC has a "big result" setting, but I'm not sure what that doesn.

>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.
>
[JS] LAMP or WAMP would use a completely different technique. Either you don't 
count on data consistency, or you explicitly throw a lock around your records. 
This bypasses the problem altogether.

>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.
>
[JS] I've been thinking about alternate approaches as well, but only three 
come to mind:

- Rewrite the whole darned thing as web pages (a monstrous chore for me, I'd 
have to use a lot of Ajax)
- Rewrite the whole darned thing in some client language (VB, C#), which would 
be an even more monstrous chore
- Use pass-through queries and hope to finesse the problem that way (a tedious 
chore, and a maintenance headache)

As for limiting access, you'd have to use views to accomplish that.

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



>Regards,
>
>
>Lawson Cronlund
>[email protected]
>+1(480)308-0641
>
>
>-----Original Message-----
>From: [email protected] [mailto:[email protected]]
>Sent: Friday, January 14, 2011 8: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]
>
>
>--
>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]