MS Access/MyODBC Interface Issue

------=_NextPart_000_0075_01CBB057.4980C410
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

If this should be posted to another forum, please let me know. I'm posting
it here because of the interaction with MyODBC/MySQL and the apparent
difference between a view and a table in primary key identification.



I am experiencing a peculiar problem and have not been able to find a
description/solution that matches what I'm seeing.



The problem:

I have a MS Access 2007 being used as a front end to a MySQL database view
using ODBC.



MySQL: MySQL 5.1.49-1ubuintu8.1

ODBC MySQL ODBC 5.1 Driver - Version 5.01.08.00

MS Access 2007 - most recent patches as of 1/9/2011



The MySQL database has one main table, some supporting table for foreign key
validation/control of some column constraints and a number of views that are
used to limit the visibility via userid/password login to the main table.



The main table has a primary key defined (int not bigint) and a timestamp
field. All time associated fields are defined as datetime. There are no
bigint columns.



Each of the normal userids is provided privileges to only one of the views.



When logged in to the database via MS Access using one of the single view
only userids, I can make a change to a simple varchar (not constrained)
field in a row, move off the row successfully, and verify that the change
was made in the MySQL data table.



However, if I do the same thing (in MS Access) up to the point of moving off
the changed row (presumably committing the change) , and then return to the
same row and try to undo the change, I get an error from MS Access saying
"The data has been changed -- Another user edited this record and saved
the changes before you attempted to save your changes. Re-edit the record".
If I click OK and then go to the row in question again, the second time the
change can be made.



Obviously, having an error come up for simple editing is not a good thing.



This does not happen when accessing the main table directly as a table (not
through a view). When I set up the linked tables in Access it identifies
the primary key in the table but needs me to manually specify the primary
key in the view. It asks for a column that it can use as a unique
identifier and lists the fields when I choose the objects to link to.



Is the primary key not identified in a vie or is there something I should do
to identify it?



Thanks in advance for any help you can offer.





Lawson Cronlund

lawson@vrtinc.com

+1(480)308-0641

+1(602)996-0376 (fax)

Voice Response Technologies, Inc.

5717 E. Justine Rd.

Scottsdale, AZ 85254




------=_NextPart_000_0075_01CBB057.4980C410--

RE: MS Access/MyODBC Interface Issue

You've come to the right place.

Unfortunately, your problem is not related to (or, at least, restricted to)
using views. That's a red herring. If you look around the web you'll see that
this is a common problem with no clear solution. Some of the suggestions I've
seen verge on magical thinking.

I've been working for two weeks to clear this up, and to get rid of the even
more frightening error 3197. After much horsing around with my application
design, I decided to return to one of my first principles: there is no point
in arguing with the computer. The computer might not be "right," but it isn't
going to change its mind.

One thing I have come to understand, although I can't say that it leads to a
real solution, is that it is very easy to modify data without thinking about
what is really going on. Here are some things I've concluded:

- Unless you are using pass-through queries, you are generally working on a
local cache of the data.
- It doesn't necessarily mean that someone else edited the data. You,
yourself, are editing data. That dirties the cache, and Access gets
justifiably concerned.
- For example, if you insert (append, in Access terms) a record you will
often not be able to find that record by scrolling back and forth.
- So far as I know your data is always saved even after getting one of the
errors. It scares the heck out of the users, of course.

I don't know if I'm on the right track or not, but

- I added a timestamp field to every table that I edit. This is recommended
all over the web, although it doesn't seem to be a universal solution.
- I have larded my code with .Refresh. In my application, that is causing
performance problem; but my immediate concern is to get the application
working.
- Sometimes I use a pass-through query instead of working with a recordset.
They are harder to maintain, but I'm used to it from using other things like
PHP.
- In those cases where I have two forms working on overlapping sets of data,
I update the other form rather than updating the underlying data.
- If at all possible, test in a multi-user environment. I got some nasty
surprises moving from my test environment to production.

As I said, I don't know for sure that these techniques will always, or even
often, work; but that's where I am.

I hope it helps.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>Sent: Monday, January 10, 2011 1:45 AM
>To: myodbc@lists.mysql.com
>Subject: MS Access/MyODBC Interface Issue
>
>If this should be posted to another forum, please let me know. I'm posting
>it here because of the interaction with MyODBC/MySQL and the apparent
>difference between a view and a table in primary key identification.
>
>
>
>I am experiencing a peculiar problem and have not been able to find a
>description/solution that matches what I'm seeing.
>
>
>
>The problem:
>
>I have a MS Access 2007 being used as a front end to a MySQL database view
>using ODBC.
>
>
>
>MySQL: MySQL 5.1.49-1ubuintu8.1
>
>ODBC MySQL ODBC 5.1 Driver - Version 5.01.08.00
>
>MS Access 2007 - most recent patches as of 1/9/2011
>
>
>
>The MySQL database has one main table, some supporting table for foreign key
>validation/control of some column constraints and a number of views that are
>used to limit the visibility via userid/password login to the main table.
>
>
>
>The main table has a primary key defined (int not bigint) and a timestamp
>field. All time associated fields are defined as datetime. There are no
>bigint columns.
>
>
>
>Each of the normal userids is provided privileges to only one of the views.
>
>
>
>When logged in to the database via MS Access using one of the single view
>only userids, I can make a change to a simple varchar (not constrained)
>field in a row, move off the row successfully, and verify that the change
>was made in the MySQL data table.
>
>
>
>However, if I do the same thing (in MS Access) up to the point of moving off
>the changed row (presumably committing the change) , and then return to the
>same row and try to undo the change, I get an error from MS Access saying
>"The data has been changed -- Another user edited this record and saved
>the changes before you attempted to save your changes. Re-edit the record".
>If I click OK and then go to the row in question again, the second time the
>change can be made.
>
>
>
>Obviously, having an error come up for simple editing is not a good thing.
>
>
>
>This does not happen when accessing the main table directly as a table (not
>through a view). When I set up the linked tables in Access it identifies
>the primary key in the table but needs me to manually specify the primary
>key in the view. It asks for a column that it can use as a unique
>identifier and lists the fields when I choose the objects to link to.
>
>
>
>Is the primary key not identified in a vie or is there something I should do
>to identify it?
>
>
>
>Thanks in advance for any help you can offer.
>
>
>
>
>
>Lawson Cronlund
>
>lawson@vrtinc.com
>
>+1(480)308-0641
>
>+1(602)996-0376 (fax)
>
>Voice Response Technologies, Inc.
>
>5717 E. Justine Rd.
>
>Scottsdale, AZ 85254
>
>





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: MS Access/MyODBC Interface Issue

Jerry,

Thanks for the reply.

I look at this problem slightly differently. I've determined that the
problem doesn't occur when you deal directly with the underlying table.
This is presumably because MS Access is formally aware of the primary =
key
since that's the only difference.

So, if MySQL is able to apply underlying indices to the view (at least =
for
the primary key) this wouldn't happen.

And, if MS Access could interpret the selection of the primary index =
it's
given during the linking of the table as a primary key, this wouldn't
happen.

I'm sure that I'm speaking from a na=EFve viewpoint since I don't have a
thorough understanding of database technology but I can tell that the
presence of the primary key index in the underlying table would resolve =
this
problem if it could somehow be inherited by the view.

Just to be clear, the VIEW in this case is strictly a SELECT * FROM a =
single
table that has a primary key.

Regards,


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

-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii.co.jp]=20
Sent: Monday, January 10, 2011 8:23 AM
To: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
Subject: RE: MS Access/MyODBC Interface Issue

You've come to the right place.

Unfortunately, your problem is not related to (or, at least, restricted =
to)=20
using views. That's a red herring. If you look around the web you'll see
that=20
this is a common problem with no clear solution. Some of the suggestions
I've=20
seen verge on magical thinking.

I've been working for two weeks to clear this up, and to get rid of the =
even

more frightening error 3197. After much horsing around with my =
application=20
design, I decided to return to one of my first principles: there is no =
point

in arguing with the computer. The computer might not be "right," but it
isn't=20
going to change its mind.

One thing I have come to understand, although I can't say that it leads =
to a

real solution, is that it is very easy to modify data without thinking =
about

what is really going on. Here are some things I've concluded:

- Unless you are using pass-through queries, you are generally working =
on a

local cache of the data.
- It doesn't necessarily mean that someone else edited the data. You,=20
yourself, are editing data. That dirties the cache, and Access gets=20
justifiably concerned.
- For example, if you insert (append, in Access terms) a record you =
will=20
often not be able to find that record by scrolling back and forth.
- So far as I know your data is always saved even after getting one of =
the=20
errors. It scares the heck out of the users, of course.

I don't know if I'm on the right track or not, but

- I added a timestamp field to every table that I edit. This is =
recommended

all over the web, although it doesn't seem to be a universal solution.
- I have larded my code with .Refresh. In my application, that is =
causing=20
performance problem; but my immediate concern is to get the application=20
working.
- Sometimes I use a pass-through query instead of working with a =
recordset.

They are harder to maintain, but I'm used to it from using other things =
like

PHP.
- In those cases where I have two forms working on overlapping sets of
data,=20
I update the other form rather than updating the underlying data.
- If at all possible, test in a multi-user environment. I got some =
nasty=20
surprises moving from my test environment to production.

As I said, I don't know for sure that these techniques will always, or =
even=20
often, work; but that's where I am.

I hope it helps.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>Sent: Monday, January 10, 2011 1:45 AM
>To: myodbc@lists.mysql.com
>Subject: MS Access/MyODBC Interface Issue
>
>If this should be posted to another forum, please let me know. I'm =
posting
>it here because of the interaction with MyODBC/MySQL and the apparent
>difference between a view and a table in primary key identification.
>
>
>
>I am experiencing a peculiar problem and have not been able to find a
>description/solution that matches what I'm seeing.
>
>
>
>The problem:
>
>I have a MS Access 2007 being used as a front end to a MySQL database =
view
>using ODBC.
>
>
>
>MySQL: MySQL 5.1.49-1ubuintu8.1
>
>ODBC MySQL ODBC 5.1 Driver - Version 5.01.08.00
>
>MS Access 2007 - most recent patches as of 1/9/2011
>
>
>
>The MySQL database has one main table, some supporting table for =
foreign
key
>validation/control of some column constraints and a number of views =
that
are
>used to limit the visibility via userid/password login to the main =
table.
>
>
>
>The main table has a primary key defined (int not bigint) and a =
timestamp
>field. All time associated fields are defined as datetime. There are =
no
>bigint columns.
>
>
>
>Each of the normal userids is provided privileges to only one of the =
views.
>
>
>
>When logged in to the database via MS Access using one of the single =
view
>only userids, I can make a change to a simple varchar (not constrained)
>field in a row, move off the row successfully, and verify that the =
change
>was made in the MySQL data table.
>
>
>
>However, if I do the same thing (in MS Access) up to the point of =
moving
off
>the changed row (presumably committing the change) , and then return to =
the
>same row and try to undo the change, I get an error from MS Access =
saying
>"The data has been changed -- Another user edited this record and =
saved
>the changes before you attempted to save your changes. Re-edit the
record".
>If I click OK and then go to the row in question again, the second =
time
the
>change can be made.
>
>
>
>Obviously, having an error come up for simple editing is not a good =
thing.
>
>
>
>This does not happen when accessing the main table directly as a table =
(not
>through a view). When I set up the linked tables in Access it =
identifies
>the primary key in the table but needs me to manually specify the =
primary
>key in the view. It asks for a column that it can use as a unique
>identifier and lists the fields when I choose the objects to link to.
>
>
>
>Is the primary key not identified in a vie or is there something I =
should
do
>to identify it?
>
>
>
>Thanks in advance for any help you can offer.
>
>
>
>
>
>Lawson Cronlund
>
>lawson@vrtinc.com
>
>+1(480)308-0641
>
>+1(602)996-0376 (fax)
>
>Voice Response Technologies, Inc.
>
>5717 E. Justine Rd.
>
>Scottsdale, AZ 85254
>
>





--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: =
http://lists.mysql.com/myodbc?unsub=3Dlawson@vrtinc.com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: MS Access/MyODBC Interface Issue

>-----Original Message-----
>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>Sent: Tuesday, January 11, 2011 10:14 PM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>Subject: RE: MS Access/MyODBC Interface Issue
>
>Jerry,
>
>Thanks for the reply.
>
>I look at this problem slightly differently. I've determined that the
>problem doesn't occur when you deal directly with the underlying table.


[JS] That, I believe, is just luck.

>This is presumably because MS Access is formally aware of the primary key
>since that's the only difference.
>
>So, if MySQL is able to apply underlying indices to the view (at least for
>the primary key) this wouldn't happen.
>
[JS] Every table I use has a primary key, and I'm not using views. That being
said, many of my forms are based upon queries of one kind or another.

>And, if MS Access could interpret the selection of the primary index it's
>given during the linking of the table as a primary key, this wouldn't
>happen.
>
[JS] I'm not sure about that, but now you have me thinking. In Access, as
opposed to MySQL, you can update the result of a SELECT query (or even nested
SELECT queries). It must, in some way, be treating that result set as a view.

That raises an obvious question: what are the "keys" that Access uses for the
result of a multi-table SELECT query? I think this is more of a
performance-related issue, though.

>I'm sure that I'm speaking from a naïve viewpoint since I don't have a
>thorough understanding of database technology but I can tell that the
>presence of the primary key index in the underlying table would resolve this
>problem if it could somehow be inherited by the view.
>
[JS] Well, as I said the problem isn't restricted to the use of MySQL views. I
still believe that it is related to the local caching and synchronization that
has to be done. I can see this in a number of ways, the most obvious of which
is that when I insert a record (using a form) the record will show as #DELETED
until I refresh.

Nice use of the dieresis, by the way.

As time permits, I'm going to be running some ODBC traces so perhaps that will
shed light on what's going on. I really need to put some speed into this
application, because it can take almost a minute for some of my forms to
refresh.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




>Just to be clear, the VIEW in this case is strictly a SELECT * FROM a single
>table that has a primary key.
>
>Regards,
>
>
>Lawson Cronlund
>lawson@vrtinc.com
>+1(480)308-0641
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Monday, January 10, 2011 8:23 AM
>To: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>Subject: RE: MS Access/MyODBC Interface Issue
>
>You've come to the right place.
>
>Unfortunately, your problem is not related to (or, at least, restricted to)
>using views. That's a red herring. If you look around the web you'll see
>that
>this is a common problem with no clear solution. Some of the suggestions
>I've
>seen verge on magical thinking.
>
>I've been working for two weeks to clear this up, and to get rid of the even
>
>more frightening error 3197. After much horsing around with my application
>design, I decided to return to one of my first principles: there is no point
>
>in arguing with the computer. The computer might not be "right," but it
>isn't
>going to change its mind.
>
>One thing I have come to understand, although I can't say that it leads to a
>
>real solution, is that it is very easy to modify data without thinking about
>
>what is really going on. Here are some things I've concluded:
>
>- Unless you are using pass-through queries, you are generally working on a
>
>local cache of the data.
>- It doesn't necessarily mean that someone else edited the data. You,
>yourself, are editing data. That dirties the cache, and Access gets
>justifiably concerned.
>- For example, if you insert (append, in Access terms) a record you will
>often not be able to find that record by scrolling back and forth.
>- So far as I know your data is always saved even after getting one of the
>errors. It scares the heck out of the users, of course.
>
>I don't know if I'm on the right track or not, but
>
>- I added a timestamp field to every table that I edit. This is recommended
>
>all over the web, although it doesn't seem to be a universal solution.
>- I have larded my code with .Refresh. In my application, that is causing
>performance problem; but my immediate concern is to get the application
>working.
>- Sometimes I use a pass-through query instead of working with a recordset.
>
>They are harder to maintain, but I'm used to it from using other things like
>
>PHP.
>- In those cases where I have two forms working on overlapping sets of
>data,
>I update the other form rather than updating the underlying data.
>- If at all possible, test in a multi-user environment. I got some nasty
>surprises moving from my test environment to production.
>
>As I said, I don't know for sure that these techniques will always, or even
>often, work; but that's where I am.
>
>I hope it helps.
>
>Regards,
>
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>E-mail: jerry@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>>-----Original Message-----
>>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>>Sent: Monday, January 10, 2011 1:45 AM
>>To: myodbc@lists.mysql.com
>>Subject: MS Access/MyODBC Interface Issue
>>
>>If this should be posted to another forum, please let me know. I'm posting
>>it here because of the interaction with MyODBC/MySQL and the apparent
>>difference between a view and a table in primary key identification.
>>
>>
>>
>>I am experiencing a peculiar problem and have not been able to find a
>>description/solution that matches what I'm seeing.
>>
>>
>>
>>The problem:
>>
>>I have a MS Access 2007 being used as a front end to a MySQL database view
>>using ODBC.
>>
>>
>>
>>MySQL: MySQL 5.1.49-1ubuintu8.1
>>
>>ODBC MySQL ODBC 5.1 Driver - Version 5.01.08.00
>>
>>MS Access 2007 - most recent patches as of 1/9/2011
>>
>>
>>
>>The MySQL database has one main table, some supporting table for foreign
>key
>>validation/control of some column constraints and a number of views that
>are
>>used to limit the visibility via userid/password login to the main table.
>>
>>
>>
>>The main table has a primary key defined (int not bigint) and a timestamp
>>field. All time associated fields are defined as datetime. There are no
>>bigint columns.
>>
>>
>>
>>Each of the normal userids is provided privileges to only one of the views.
>>
>>
>>
>>When logged in to the database via MS Access using one of the single view
>>only userids, I can make a change to a simple varchar (not constrained)
>>field in a row, move off the row successfully, and verify that the change
>>was made in the MySQL data table.
>>
>>
>>
>>However, if I do the same thing (in MS Access) up to the point of moving
>off
>>the changed row (presumably committing the change) , and then return to the
>>same row and try to undo the change, I get an error from MS Access saying
>>"The data has been changed -- Another user edited this record and saved
>>the changes before you attempted to save your changes. Re-edit the
>record".
>>If I click OK and then go to the row in question again, the second time
>the
>>change can be made.
>>
>>
>>
>>Obviously, having an error come up for simple editing is not a good thing.
>>
>>
>>
>>This does not happen when accessing the main table directly as a table (not
>>through a view). When I set up the linked tables in Access it identifies
>>the primary key in the table but needs me to manually specify the primary
>>key in the view. It asks for a column that it can use as a unique
>>identifier and lists the fields when I choose the objects to link to.
>>
>>
>>
>>Is the primary key not identified in a vie or is there something I should
>do
>>to identify it?
>>
>>
>>
>>Thanks in advance for any help you can offer.
>>
>>
>>
>>
>>
>>Lawson Cronlund
>>
>>lawson@vrtinc.com
>>
>>+1(480)308-0641
>>
>>+1(602)996-0376 (fax)
>>
>>Voice Response Technologies, Inc.
>>
>>5717 E. Justine Rd.
>>
>>Scottsdale, AZ 85254
>>
>>
>
>
>
>
>
>--
>MySQL ODBC Mailing List
>For list archives: http://lists.mysql.com/myodbc
>To unsubscribe: http://lists.mysql.com/myodbc?unsub=lawson@vrtinc.com





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: MS Access/MyODBC Interface Issue

I suggest also checking the MySQL Query Log in addition to the ODBC tr=
aces
(which you can easyly turn on with MySQL WorkBench). For extensive quer=
ies,
the former will be much faster than the latter.

While checking the MySQL Log, I noticed that a lot of my join queries w=
ould
simply download the entire tables and then do a "local join" on cached
items. This, of course, yields to absolutely terrible performance - wor=
st
than Native Access tables. What triggers the joins to become "multiple
selects", I have no idea, as some queries went through just fine. The
performaces went from Native Access on shared drive of ~7s to "download=
all
tables and do local join" of ~15 seconds to passthrough query of ~0.5
seconds...

To work around this issue I had to resolve to passthrough Queries, howe=
ver
this means that Access no longer has any references to the indexes so y=
ou
have to perform updates and deletes with code. Anyway, as mentioned in
previous emails, sometimes can't even figure those out on it's own.

Through some ODBC trace inspections (though MySQL Query logging could h=
ave
shown me this too) for normal tables that have the "Record is locked"
issue, I found out that for some tables, Access seems to simply ignore =
the
index and tries to do an update statement using a where statement
containing the entire row data as conditions!

- Sebastien Caisse



From: "Jerry Schwartz"
To: "'Lawson K. Cronlund'" ,

Date: 2011/01/12 10:01
Subject: RE: MS Access/MyODBC Interface Issue



>-----Original Message-----
>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>Sent: Tuesday, January 11, 2011 10:14 PM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>Subject: RE: MS Access/MyODBC Interface Issue
>
>Jerry,
>
>Thanks for the reply.
>
>I look at this problem slightly differently. I've determined that the=

>problem doesn't occur when you deal directly with the underlying table=
..


[JS] That, I believe, is just luck.

>This is presumably because MS Access is formally aware of the primary =
key
>since that's the only difference.
>
>So, if MySQL is able to apply underlying indices to the view (at least=
for
>the primary key) this wouldn't happen.
>
[JS] Every table I use has a primary key, and I'm not using views. That=

being
said, many of my forms are based upon queries of one kind or another.

>And, if MS Access could interpret the selection of the primary index i=
t's
>given during the linking of the table as a primary key, this wouldn't
>happen.
>
[JS] I'm not sure about that, but now you have me thinking. In Access,=
as
opposed to MySQL, you can update the result of a SELECT query (or even
nested
SELECT queries). It must, in some way, be treating that result set as a=

view.

That raises an obvious question: what are the "keys" that Access uses f=
or
the
result of a multi-table SELECT query? I think this is more of a
performance-related issue, though.

>I'm sure that I'm speaking from a na=EFve viewpoint since I don't have=
a
>thorough understanding of database technology but I can tell that the
>presence of the primary key index in the underlying table would resolv=
e
this
>problem if it could somehow be inherited by the view.
>
[JS] Well, as I said the problem isn't restricted to the use of MySQL
views. I
still believe that it is related to the local caching and synchronizati=
on
that
has to be done. I can see this in a number of ways, the most obvious of=

which
is that when I insert a record (using a form) the record will show as
#DELETED
until I refresh.

Nice use of the dieresis, by the way.

As time permits, I'm going to be running some ODBC traces so perhaps th=
at
will
shed light on what's going on. I really need to put some speed into thi=
s
application, because it can take almost a minute for some of my forms t=
o
refresh.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




>Just to be clear, the VIEW in this case is strictly a SELECT * FROM a
single
>table that has a primary key.
>
>Regards,
>
>
>Lawson Cronlund
>lawson@vrtinc.com
>+1(480)308-0641
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Monday, January 10, 2011 8:23 AM
>To: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>Subject: RE: MS Access/MyODBC Interface Issue
>
>You've come to the right place.
>
>Unfortunately, your problem is not related to (or, at least, restricte=
d
to)
>using views. That's a red herring. If you look around the web you'll s=
ee
>that
>this is a common problem with no clear solution. Some of the suggestio=
ns
>I've
>seen verge on magical thinking.
>
>I've been working for two weeks to clear this up, and to get rid of th=
e
even
>
>more frightening error 3197. After much horsing around with my applica=
tion
>design, I decided to return to one of my first principles: there is no=

point
>
>in arguing with the computer. The computer might not be "right," but i=
t
>isn't
>going to change its mind.
>
>One thing I have come to understand, although I can't say that it lead=
s to
a
>
>real solution, is that it is very easy to modify data without thinking=

about
>
>what is really going on. Here are some things I've concluded:
>
>- Unless you are using pass-through queries, you are generally workin=
g on
a
>
>local cache of the data.
>- It doesn't necessarily mean that someone else edited the data. You,=

>yourself, are editing data. That dirties the cache, and Access gets
>justifiably concerned.
>- For example, if you insert (append, in Access terms) a record you w=
ill
>often not be able to find that record by scrolling back and forth.
>- So far as I know your data is always saved even after getting one o=
f
the
>errors. It scares the heck out of the users, of course.
>
>I don't know if I'm on the right track or not, but
>
>- I added a timestamp field to every table that I edit. This is
recommended
>
>all over the web, although it doesn't seem to be a universal solution.=

>- I have larded my code with .Refresh. In my application, that is cau=
sing
>performance problem; but my immediate concern is to get the applicatio=
n
>working.
>- Sometimes I use a pass-through query instead of working with a
recordset.
>
>They are harder to maintain, but I'm used to it from using other thing=
s
like
>
>PHP.
>- In those cases where I have two forms working on overlapping sets o=
f
>data,
>I update the other form rather than updating the underlying data.
>- If at all possible, test in a multi-user environment. I got some na=
sty
>surprises moving from my test environment to production.
>
>As I said, I don't know for sure that these techniques will always, or=

even
>often, work; but that's where I am.
>
>I hope it helps.
>
>Regards,
>
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>E-mail: jerry@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>>-----Original Message-----
>>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>>Sent: Monday, January 10, 2011 1:45 AM
>>To: myodbc@lists.mysql.com
>>Subject: MS Access/MyODBC Interface Issue
>>
>>If this should be posted to another forum, please let me know. I'm
posting
>>it here because of the interaction with MyODBC/MySQL and the apparent=

>>difference between a view and a table in primary key identification.
>>
>>
>>
>>I am experiencing a peculiar problem and have not been able to find a=

>>description/solution that matches what I'm seeing.
>>
>>
>>
>>The problem:
>>
>>I have a MS Access 2007 being used as a front end to a MySQL database=

view
>>using ODBC.
>>
>>
>>
>>MySQL: MySQL 5.1.49-1ubuintu8.1
>>
>>ODBC MySQL ODBC 5.1 Driver - Version 5.01.08.00
>>
>>MS Access 2007 - most recent patches as of 1/9/2011
>>
>>
>>
>>The MySQL database has one main table, some supporting table for fore=
ign
>key
>>validation/control of some column constraints and a number of views t=
hat
>are
>>used to limit the visibility via userid/password login to the main ta=
ble.
>>
>>
>>
>>The main table has a primary key defined (int not bigint) and a times=
tamp
>>field. All time associated fields are defined as datetime. There ar=
e no
>>bigint columns.
>>
>>
>>
>>Each of the normal userids is provided privileges to only one of the
views.
>>
>>
>>
>>When logged in to the database via MS Access using one of the single =
view
>>only userids, I can make a change to a simple varchar (not constraine=
d)
>>field in a row, move off the row successfully, and verify that the ch=
ange
>>was made in the MySQL data table.
>>
>>
>>
>>However, if I do the same thing (in MS Access) up to the point of mov=
ing
>off
>>the changed row (presumably committing the change) , and then return =
to
the
>>same row and try to undo the change, I get an error from MS Access sa=
ying
>>"The data has been changed -- Another user edited this record and s=
aved
>>the changes before you attempted to save your changes. Re-edit the
>record".
>>If I click OK and then go to the row in question again, the second t=
ime
>the
>>change can be made.
>>
>>
>>
>>Obviously, having an error come up for simple editing is not a good
thing.
>>
>>
>>
>>This does not happen when accessing the main table directly as a tabl=
e
(not
>>through a view). When I set up the linked tables in Access it identi=
fies
>>the primary key in the table but needs me to manually specify the pri=
mary
>>key in the view. It asks for a column that it can use as a unique
>>identifier and lists the fields when I choose the objects to link to.=

>>
>>
>>
>>Is the primary key not identified in a vie or is there something I sh=
ould
>do
>>to identify it?
>>
>>
>>
>>Thanks in advance for any help you can offer.
>>
>>
>>
>>
>>
>>Lawson Cronlund
>>
>>lawson@vrtinc.com
>>
>>+1(480)308-0641
>>
>>+1(602)996-0376 (fax)
>>
>>Voice Response Technologies, Inc.
>>
>>5717 E. Justine Rd.
>>
>>Scottsdale, AZ 85254
>>
>>
>
>
>
>
>
>--
>MySQL ODBC Mailing List
>For list archives: http://lists.mysql.com/myodbc
>To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dlawson@vrtinc=
..com





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:
http://lists.mysql.com/myodbc?unsub=3Dscaisse@ts.jgh.mcgill. ca

=



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: MS Access/MyODBC Interface Issue

I expect to see the same thing you saw.

Our performance is terrible (except for a few things that went from being slow
to being instantaneous), but I've been concentrating on just getting things
working. This is a combination sales, product, CRM, and order entry system. If
it doesn't work, neither does anyone in our office.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: scaisse@jgh.mcgill.ca [mailto:scaisse@jgh.mcgill.ca]
>Sent: Wednesday, January 12, 2011 11:03 AM
>To: Jerry Schwartz
>Cc: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>Subject: RE: MS Access/MyODBC Interface Issue
>
>I suggest also checking the MySQL Query Log in addition to the ODBC traces
>(which you can easyly turn on with MySQL WorkBench). For extensive queries,
>the former will be much faster than the latter.
>
>While checking the MySQL Log, I noticed that a lot of my join queries would
>simply download the entire tables and then do a "local join" on cached
>items. This, of course, yields to absolutely terrible performance - worst
>than Native Access tables. What triggers the joins to become "multiple
>selects", I have no idea, as some queries went through just fine. The
>performaces went from Native Access on shared drive of ~7s to "download all
>tables and do local join" of ~15 seconds to passthrough query of ~0.5
>seconds...
>
>To work around this issue I had to resolve to passthrough Queries, however
>this means that Access no longer has any references to the indexes so you
>have to perform updates and deletes with code. Anyway, as mentioned in
>previous emails, sometimes can't even figure those out on it's own.
>
>Through some ODBC trace inspections (though MySQL Query logging could have
>shown me this too) for normal tables that have the "Record is locked"
>issue, I found out that for some tables, Access seems to simply ignore the
>index and tries to do an update statement using a where statement
>containing the entire row data as conditions!
>
>- Sebastien Caisse
>
>
>
>From: "Jerry Schwartz"
>To: "'Lawson K. Cronlund'" ,
>
>Date: 2011/01/12 10:01
>Subject: RE: MS Access/MyODBC Interface Issue
>
>
>
>>-----Original Message-----
>>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>>Sent: Tuesday, January 11, 2011 10:14 PM
>>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>>Subject: RE: MS Access/MyODBC Interface Issue
>>
>>Jerry,
>>
>>Thanks for the reply.
>>
>>I look at this problem slightly differently. I've determined that the
>>problem doesn't occur when you deal directly with the underlying table.
>
>
>[JS] That, I believe, is just luck.
>
>>This is presumably because MS Access is formally aware of the primary key
>>since that's the only difference.
>>
>>So, if MySQL is able to apply underlying indices to the view (at least for
>>the primary key) this wouldn't happen.
>>
>[JS] Every table I use has a primary key, and I'm not using views. That
>being
>said, many of my forms are based upon queries of one kind or another.
>
>>And, if MS Access could interpret the selection of the primary index it's
>>given during the linking of the table as a primary key, this wouldn't
>>happen.
>>
>[JS] I'm not sure about that, but now you have me thinking. In Access, as
>opposed to MySQL, you can update the result of a SELECT query (or even
>nested
>SELECT queries). It must, in some way, be treating that result set as a
>view.
>
>That raises an obvious question: what are the "keys" that Access uses for
>the
>result of a multi-table SELECT query? I think this is more of a
>performance-related issue, though.
>
>>I'm sure that I'm speaking from a naïve viewpoint since I don't have a
>>thorough understanding of database technology but I can tell that the
>>presence of the primary key index in the underlying table would resolve
>this
>>problem if it could somehow be inherited by the view.
>>
>[JS] Well, as I said the problem isn't restricted to the use of MySQL
>views. I
>still believe that it is related to the local caching and synchronization
>that
>has to be done. I can see this in a number of ways, the most obvious of
>which
>is that when I insert a record (using a form) the record will show as
>#DELETED
>until I refresh.
>
>Nice use of the dieresis, by the way.
>
>As time permits, I'm going to be running some ODBC traces so perhaps that
>will
>shed light on what's going on. I really need to put some speed into this
>application, because it can take almost a minute for some of my forms to
>refresh.
>
>Regards,
>
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>E-mail: jerry@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>
>
>>Just to be clear, the VIEW in this case is strictly a SELECT * FROM a
>single
>>table that has a primary key.
>>
>>Regards,
>>
>>
>>Lawson Cronlund
>>lawson@vrtinc.com
>>+1(480)308-0641
>>
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>>Sent: Monday, January 10, 2011 8:23 AM
>>To: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>>Subject: RE: MS Access/MyODBC Interface Issue
>>
>>You've come to the right place.
>>
>>Unfortunately, your problem is not related to (or, at least, restricted
>to)
>>using views. That's a red herring. If you look around the web you'll see
>>that
>>this is a common problem with no clear solution. Some of the suggestions
>>I've
>>seen verge on magical thinking.
>>
>>I've been working for two weeks to clear this up, and to get rid of the
>even
>>
>>more frightening error 3197. After much horsing around with my application
>>design, I decided to return to one of my first principles: there is no
>point
>>
>>in arguing with the computer. The computer might not be "right," but it
>>isn't
>>going to change its mind.
>>
>>One thing I have come to understand, although I can't say that it leads to
>a
>>
>>real solution, is that it is very easy to modify data without thinking
>about
>>
>>what is really going on. Here are some things I've concluded:
>>
>>- Unless you are using pass-through queries, you are generally working on
>a
>>
>>local cache of the data.
>>- It doesn't necessarily mean that someone else edited the data. You,
>>yourself, are editing data. That dirties the cache, and Access gets
>>justifiably concerned.
>>- For example, if you insert (append, in Access terms) a record you will
>>often not be able to find that record by scrolling back and forth.
>>- So far as I know your data is always saved even after getting one of
>the
>>errors. It scares the heck out of the users, of course.
>>
>>I don't know if I'm on the right track or not, but
>>
>>- I added a timestamp field to every table that I edit. This is
>recommended
>>
>>all over the web, although it doesn't seem to be a universal solution.
>>- I have larded my code with .Refresh. In my application, that is causing
>>performance problem; but my immediate concern is to get the application
>>working.
>>- Sometimes I use a pass-through query instead of working with a
>recordset.
>>
>>They are harder to maintain, but I'm used to it from using other things
>like
>>
>>PHP.
>>- In those cases where I have two forms working on overlapping sets of
>>data,
>>I update the other form rather than updating the underlying data.
>>- If at all possible, test in a multi-user environment. I got some nasty
>>surprises moving from my test environment to production.
>>
>>As I said, I don't know for sure that these techniques will always, or
>even
>>often, work; but that's where I am.
>>
>>I hope it helps.
>>
>>Regards,
>>
>>Jerry Schwartz
>>Global Information Incorporated
>>195 Farmington Ave.
>>Farmington, CT 06032
>>
>>860.674.8796 / FAX: 860.674.8341
>>E-mail: jerry@gii.co.jp
>>Web site: www.the-infoshop.com
>>
>>
>>>-----Original Message-----
>>>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>>>Sent: Monday, January 10, 2011 1:45 AM
>>>To: myodbc@lists.mysql.com
>>>Subject: MS Access/MyODBC Interface Issue
>>>
>>>If this should be posted to another forum, please let me know. I'm
>posting
>>>it here because of the interaction with MyODBC/MySQL and the apparent
>>>difference between a view and a table in primary key identification.
>>>
>>>
>>>
>>>I am experiencing a peculiar problem and have not been able to find a
>>>description/solution that matches what I'm seeing.
>>>
>>>
>>>
>>>The problem:
>>>
>>>I have a MS Access 2007 being used as a front end to a MySQL database
>view
>>>using ODBC.
>>>
>>>
>>>
>>>MySQL: MySQL 5.1.49-1ubuintu8.1
>>>
>>>ODBC MySQL ODBC 5.1 Driver - Version 5.01.08.00
>>>
>>>MS Access 2007 - most recent patches as of 1/9/2011
>>>
>>>
>>>
>>>The MySQL database has one main table, some supporting table for foreign
>>key
>>>validation/control of some column constraints and a number of views that
>>are
>>>used to limit the visibility via userid/password login to the main table.
>>>
>>>
>>>
>>>The main table has a primary key defined (int not bigint) and a timestamp
>>>field. All time associated fields are defined as datetime. There are no
>>>bigint columns.
>>>
>>>
>>>
>>>Each of the normal userids is provided privileges to only one of the
>views.
>>>
>>>
>>>
>>>When logged in to the database via MS Access using one of the single view
>>>only userids, I can make a change to a simple varchar (not constrained)
>>>field in a row, move off the row successfully, and verify that the change
>>>was made in the MySQL data table.
>>>
>>>
>>>
>>>However, if I do the same thing (in MS Access) up to the point of moving
>>off
>>>the changed row (presumably committing the change) , and then return to
>the
>>>same row and try to undo the change, I get an error from MS Access saying
>>>"The data has been changed -- Another user edited this record and saved
>>>the changes before you attempted to save your changes. Re-edit the
>>record".
>>>If I click OK and then go to the row in question again, the second time
>>the
>>>change can be made.
>>>
>>>
>>>
>>>Obviously, having an error come up for simple editing is not a good
>thing.
>>>
>>>
>>>
>>>This does not happen when accessing the main table directly as a table
>(not
>>>through a view). When I set up the linked tables in Access it identifies
>>>the primary key in the table but needs me to manually specify the primary
>>>key in the view. It asks for a column that it can use as a unique
>>>identifier and lists the fields when I choose the objects to link to.
>>>
>>>
>>>
>>>Is the primary key not identified in a vie or is there something I should
>>do
>>>to identify it?
>>>
>>>
>>>
>>>Thanks in advance for any help you can offer.
>>>
>>>
>>>
>>>
>>>
>>>Lawson Cronlund
>>>
>>>lawson@vrtinc.com
>>>
>>>+1(480)308-0641
>>>
>>>+1(602)996-0376 (fax)
>>>
>>>Voice Response Technologies, Inc.
>>>
>>>5717 E. Justine Rd.
>>>
>>>Scottsdale, AZ 85254
>>>
>>>
>>
>>
>>
>>
>>
>>--
>>MySQL ODBC Mailing List
>>For list archives: http://lists.mysql.com/myodbc
>>To unsubscribe: http://lists.mysql.com/myodbc?unsub=lawson@vrtinc.com
>
>
>
>
>
>--
>MySQL ODBC Mailing List
>For list archives: http://lists.mysql.com/myodbc
>To unsubscribe:
>http://lists.mysql.com/myodbc?unsub=scaisse@ts.jgh.mcgill.c a
>
>





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

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@vrtinc.com
+1(480)308-0641


-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii.co.jp]=20
Sent: Wednesday, January 12, 2011 11:56 AM
To: scaisse@jgh.mcgill.ca
Cc: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
Subject: RE: MS Access/MyODBC Interface Issue

I expect to see the same thing you saw.

Our performance is terrible (except for a few things that went from =
being
slow=20
to being instantaneous), but I've been concentrating on just getting =
things=20
working. This is a combination sales, product, CRM, and order entry =
system.
If=20
it doesn't work, neither does anyone in our office.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: scaisse@jgh.mcgill.ca [mailto:scaisse@jgh.mcgill.ca]
>Sent: Wednesday, January 12, 2011 11:03 AM
>To: Jerry Schwartz
>Cc: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>Subject: RE: MS Access/MyODBC Interface Issue
>
>I suggest also checking the MySQL Query Log in addition to the ODBC =
traces
>(which you can easyly turn on with MySQL WorkBench). For extensive =
queries,
>the former will be much faster than the latter.
>
>While checking the MySQL Log, I noticed that a lot of my join queries =
would
>simply download the entire tables and then do a "local join" on cached
>items. This, of course, yields to absolutely terrible performance - =
worst
>than Native Access tables. What triggers the joins to become "multiple
>selects", I have no idea, as some queries went through just fine. The
>performaces went from Native Access on shared drive of ~7s to "download =
all
>tables and do local join" of ~15 seconds to passthrough query of ~0.5
>seconds...
>
>To work around this issue I had to resolve to passthrough Queries, =
however
>this means that Access no longer has any references to the indexes so =
you
>have to perform updates and deletes with code. Anyway, as mentioned in
>previous emails, sometimes can't even figure those out on it's own.
>
>Through some ODBC trace inspections (though MySQL Query logging could =
have
>shown me this too) for normal tables that have the "Record is locked"
>issue, I found out that for some tables, Access seems to simply ignore =
the
>index and tries to do an update statement using a where statement
>containing the entire row data as conditions!
>
>- Sebastien Caisse
>
>
>
>From: "Jerry Schwartz"
>To: "'Lawson K. Cronlund'" ,
>
>Date: 2011/01/12 10:01
>Subject: RE: MS Access/MyODBC Interface Issue
>
>
>
>>-----Original Message-----
>>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>>Sent: Tuesday, January 11, 2011 10:14 PM
>>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>>Subject: RE: MS Access/MyODBC Interface Issue
>>
>>Jerry,
>>
>>Thanks for the reply.
>>
>>I look at this problem slightly differently. I've determined that the
>>problem doesn't occur when you deal directly with the underlying =
table.
>
>
>[JS] That, I believe, is just luck.
>
>>This is presumably because MS Access is formally aware of the primary =
key
>>since that's the only difference.
>>
>>So, if MySQL is able to apply underlying indices to the view (at least =
for
>>the primary key) this wouldn't happen.
>>
>[JS] Every table I use has a primary key, and I'm not using views. That
>being
>said, many of my forms are based upon queries of one kind or another.
>
>>And, if MS Access could interpret the selection of the primary index =
it's
>>given during the linking of the table as a primary key, this wouldn't
>>happen.
>>
>[JS] I'm not sure about that, but now you have me thinking. In Access, =
as
>opposed to MySQL, you can update the result of a SELECT query (or even
>nested
>SELECT queries). It must, in some way, be treating that result set as a
>view.
>
>That raises an obvious question: what are the "keys" that Access uses =
for
>the
>result of a multi-table SELECT query? I think this is more of a
>performance-related issue, though.
>
>>I'm sure that I'm speaking from a na=EFve viewpoint since I don't have =
a
>>thorough understanding of database technology but I can tell that the
>>presence of the primary key index in the underlying table would =
resolve
>this
>>problem if it could somehow be inherited by the view.
>>
>[JS] Well, as I said the problem isn't restricted to the use of MySQL
>views. I
>still believe that it is related to the local caching and =
synchronization
>that
>has to be done. I can see this in a number of ways, the most obvious of
>which
>is that when I insert a record (using a form) the record will show as
>#DELETED
>until I refresh.
>
>Nice use of the dieresis, by the way.
>
>As time permits, I'm going to be running some ODBC traces so perhaps =
that
>will
>shed light on what's going on. I really need to put some speed into =
this
>application, because it can take almost a minute for some of my forms =
to
>refresh.
>
>Regards,
>
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>E-mail: jerry@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>
>
>>Just to be clear, the VIEW in this case is strictly a SELECT * FROM a
>single
>>table that has a primary key.
>>
>>Regards,
>>
>>
>>Lawson Cronlund
>>lawson@vrtinc.com
>>+1(480)308-0641
>>
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>>Sent: Monday, January 10, 2011 8:23 AM
>>To: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>>Subject: RE: MS Access/MyODBC Interface Issue
>>
>>You've come to the right place.
>>
>>Unfortunately, your problem is not related to (or, at least, =
restricted
>to)
>>using views. That's a red herring. If you look around the web you'll =
see
>>that
>>this is a common problem with no clear solution. Some of the =
suggestions
>>I've
>>seen verge on magical thinking.
>>
>>I've been working for two weeks to clear this up, and to get rid of =
the
>even
>>
>>more frightening error 3197. After much horsing around with my =
application
>>design, I decided to return to one of my first principles: there is no
>point
>>
>>in arguing with the computer. The computer might not be "right," but =
it
>>isn't
>>going to change its mind.
>>
>>One thing I have come to understand, although I can't say that it =
leads to
>a
>>
>>real solution, is that it is very easy to modify data without thinking
>about
>>
>>what is really going on. Here are some things I've concluded:
>>
>>- Unless you are using pass-through queries, you are generally =
working on
>a
>>
>>local cache of the data.
>>- It doesn't necessarily mean that someone else edited the data. You,
>>yourself, are editing data. That dirties the cache, and Access gets
>>justifiably concerned.
>>- For example, if you insert (append, in Access terms) a record you =
will
>>often not be able to find that record by scrolling back and forth.
>>- So far as I know your data is always saved even after getting one =
of
>the
>>errors. It scares the heck out of the users, of course.
>>
>>I don't know if I'm on the right track or not, but
>>
>>- I added a timestamp field to every table that I edit. This is
>recommended
>>
>>all over the web, although it doesn't seem to be a universal solution.
>>- I have larded my code with .Refresh. In my application, that is =
causing
>>performance problem; but my immediate concern is to get the =
application
>>working.
>>- Sometimes I use a pass-through query instead of working with a
>recordset.
>>
>>They are harder to maintain, but I'm used to it from using other =
things
>like
>>
>>PHP.
>>- In those cases where I have two forms working on overlapping sets =
of
>>data,
>>I update the other form rather than updating the underlying data.
>>- If at all possible, test in a multi-user environment. I got some =
nasty
>>surprises moving from my test environment to production.
>>
>>As I said, I don't know for sure that these techniques will always, or
>even
>>often, work; but that's where I am.
>>
>>I hope it helps.
>>
>>Regards,
>>
>>Jerry Schwartz
>>Global Information Incorporated
>>195 Farmington Ave.
>>Farmington, CT 06032
>>
>>860.674.8796 / FAX: 860.674.8341
>>E-mail: jerry@gii.co.jp
>>Web site: www.the-infoshop.com
>>
>>
>>>-----Original Message-----
>>>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>>>Sent: Monday, January 10, 2011 1:45 AM
>>>To: myodbc@lists.mysql.com
>>>Subject: MS Access/MyODBC Interface Issue
>>>
>>>If this should be posted to another forum, please let me know. I'm
>posting
>>>it here because of the interaction with MyODBC/MySQL and the apparent
>>>difference between a view and a table in primary key identification.
>>>
>>>
>>>
>>>I am experiencing a peculiar problem and have not been able to find a
>>>description/solution that matches what I'm seeing.
>>>
>>>
>>>
>>>The problem:
>>>
>>>I have a MS Access 2007 being used as a front end to a MySQL database
>view
>>>using ODBC.
>>>
>>>
>>>
>>>MySQL: MySQL 5.1.49-1ubuintu8.1
>>>
>>>ODBC MySQL ODBC 5.1 Driver - Version 5.01.08.00
>>>
>>>MS Access 2007 - most recent patches as of 1/9/2011
>>>
>>>
>>>
>>>The MySQL database has one main table, some supporting table for =
foreign
>>key
>>>validation/control of some column constraints and a number of views =
that
>>are
>>>used to limit the visibility via userid/password login to the main =
table.
>>>
>>>
>>>
>>>The main table has a primary key defined (int not bigint) and a =
timestamp
>>>field. All time associated fields are defined as datetime. There =
are no
>>>bigint columns.
>>>
>>>
>>>
>>>Each of the normal userids is provided privileges to only one of the
>views.
>>>
>>>
>>>
>>>When logged in to the database via MS Access using one of the single =
view
>>>only userids, I can make a change to a simple varchar (not =
constrained)
>>>field in a row, move off the row successfully, and verify that the =
change
>>>was made in the MySQL data table.
>>>
>>>
>>>
>>>However, if I do the same thing (in MS Access) up to the point of =
moving
>>off
>>>the changed row (presumably committing the change) , and then return =
to
>the
>>>same row and try to undo the change, I get an error from MS Access =
saying
>>>"The data has been changed -- Another user edited this record and =
saved
>>>the changes before you attempted to save your changes. Re-edit the
>>record".
>>>If I click OK and then go to the row in question again, the second =
time
>>the
>>>change can be made.
>>>
>>>
>>>
>>>Obviously, having an error come up for simple editing is not a good
>thing.
>>>
>>>
>>>
>>>This does not happen when accessing the main table directly as a =
table
>(not
>>>through a view). When I set up the linked tables in Access it =
identifies
>>>the primary key in the table but needs me to manually specify the =
primary
>>>key in the view. It asks for a column that it can use as a unique
>>>identifier and lists the fields when I choose the objects to link to.
>>>
>>>
>>>
>>>Is the primary key not identified in a vie or is there something I =
should
>>do
>>>to identify it?
>>>
>>>
>>>
>>>Thanks in advance for any help you can offer.
>>>
>>>
>>>
>>>
>>>
>>>Lawson Cronlund
>>>
>>>lawson@vrtinc.com
>>>
>>>+1(480)308-0641
>>>
>>>+1(602)996-0376 (fax)
>>>
>>>Voice Response Technologies, Inc.
>>>
>>>5717 E. Justine Rd.
>>>
>>>Scottsdale, AZ 85254
>>>
>>>
>>
>>
>>
>>
>>
>>--
>>MySQL ODBC Mailing List
>>For list archives: http://lists.mysql.com/myodbc
>>To unsubscribe: =
http://lists.mysql.com/myodbc?unsub=3Dlawson@vrtinc.com
>
>
>
>
>
>--
>MySQL ODBC Mailing List
>For list archives: http://lists.mysql.com/myodbc
>To unsubscribe:
>http://lists.mysql.com/myodbc?unsub=3Dscaisse@ts.jgh.mcgill .ca
>
>





--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: =
http://lists.mysql.com/myodbc?unsub=3Dlawson@vrtinc.com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

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"
To: "'Jerry Schwartz'" ,
Cc:
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@vrtinc.com
+1(480)308-0641


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: MS Access/MyODBC Interface Issue

You won't like this, but the only way I've solved this problem is with a
Refresh All.

In my case, I have a lot of VB code so I put the refresh in there. I'm going
to look for an alternative, because this is very slow, but at this point I'd
rather have the users wait than get frightened.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com

>-----Original Message-----
>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>Sent: Thursday, January 13, 2011 4:47 PM
>To: 'Jerry Schwartz'; scaisse@jgh.mcgill.ca
>Cc: myodbc@lists.mysql.com
>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@vrtinc.com
>+1(480)308-0641
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Wednesday, January 12, 2011 11:56 AM
>To: scaisse@jgh.mcgill.ca
>Cc: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>Subject: RE: MS Access/MyODBC Interface Issue
>
>I expect to see the same thing you saw.
>
>Our performance is terrible (except for a few things that went from being
>slow
>to being instantaneous), but I've been concentrating on just getting things
>working. This is a combination sales, product, CRM, and order entry system.
>If
>it doesn't work, neither does anyone in our office.
>
>Regards,
>
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>E-mail: jerry@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>>-----Original Message-----
>>From: scaisse@jgh.mcgill.ca [mailto:scaisse@jgh.mcgill.ca]
>>Sent: Wednesday, January 12, 2011 11:03 AM
>>To: Jerry Schwartz
>>Cc: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>>Subject: RE: MS Access/MyODBC Interface Issue
>>
>>I suggest also checking the MySQL Query Log in addition to the ODBC traces
>>(which you can easyly turn on with MySQL WorkBench). For extensive queries,
>>the former will be much faster than the latter.
>>
>>While checking the MySQL Log, I noticed that a lot of my join queries would
>>simply download the entire tables and then do a "local join" on cached
>>items. This, of course, yields to absolutely terrible performance - worst
>>than Native Access tables. What triggers the joins to become "multiple
>>selects", I have no idea, as some queries went through just fine. The
>>performaces went from Native Access on shared drive of ~7s to "download all
>>tables and do local join" of ~15 seconds to passthrough query of ~0.5
>>seconds...
>>
>>To work around this issue I had to resolve to passthrough Queries, however
>>this means that Access no longer has any references to the indexes so you
>>have to perform updates and deletes with code. Anyway, as mentioned in
>>previous emails, sometimes can't even figure those out on it's own.
>>
>>Through some ODBC trace inspections (though MySQL Query logging could have
>>shown me this too) for normal tables that have the "Record is locked"
>>issue, I found out that for some tables, Access seems to simply ignore the
>>index and tries to do an update statement using a where statement
>>containing the entire row data as conditions!
>>
>>- Sebastien Caisse
>>
>>
>>
>>From: "Jerry Schwartz"
>>To: "'Lawson K. Cronlund'" ,
>>
>>Date: 2011/01/12 10:01
>>Subject: RE: MS Access/MyODBC Interface Issue
>>
>>
>>
>>>-----Original Message-----
>>>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>>>Sent: Tuesday, January 11, 2011 10:14 PM
>>>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>>>Subject: RE: MS Access/MyODBC Interface Issue
>>>
>>>Jerry,
>>>
>>>Thanks for the reply.
>>>
>>>I look at this problem slightly differently. I've determined that the
>>>problem doesn't occur when you deal directly with the underlying table.
>>
>>
>>[JS] That, I believe, is just luck.
>>
>>>This is presumably because MS Access is formally aware of the primary key
>>>since that's the only difference.
>>>
>>>So, if MySQL is able to apply underlying indices to the view (at least for
>>>the primary key) this wouldn't happen.
>>>
>>[JS] Every table I use has a primary key, and I'm not using views. That
>>being
>>said, many of my forms are based upon queries of one kind or another.
>>
>>>And, if MS Access could interpret the selection of the primary index it's
>>>given during the linking of the table as a primary key, this wouldn't
>>>happen.
>>>
>>[JS] I'm not sure about that, but now you have me thinking. In Access, as
>>opposed to MySQL, you can update the result of a SELECT query (or even
>>nested
>>SELECT queries). It must, in some way, be treating that result set as a
>>view.
>>
>>That raises an obvious question: what are the "keys" that Access uses for
>>the
>>result of a multi-table SELECT query? I think this is more of a
>>performance-related issue, though.
>>
>>>I'm sure that I'm speaking from a naïve viewpoint since I don't have a
>>>thorough understanding of database technology but I can tell that the
>>>presence of the primary key index in the underlying table would resolve
>>this
>>>problem if it could somehow be inherited by the view.
>>>
>>[JS] Well, as I said the problem isn't restricted to the use of MySQL
>>views. I
>>still believe that it is related to the local caching and synchronization
>>that
>>has to be done. I can see this in a number of ways, the most obvious of
>>which
>>is that when I insert a record (using a form) the record will show as
>>#DELETED
>>until I refresh.
>>
>>Nice use of the dieresis, by the way.
>>
>>As time permits, I'm going to be running some ODBC traces so perhaps that
>>will
>>shed light on what's going on. I really need to put some speed into this
>>application, because it can take almost a minute for some of my forms to
>>refresh.
>>
>>Regards,
>>
>>Jerry Schwartz
>>Global Information Incorporated
>>195 Farmington Ave.
>>Farmington, CT 06032
>>
>>860.674.8796 / FAX: 860.674.8341
>>E-mail: jerry@gii.co.jp
>>Web site: www.the-infoshop.com
>>
>>
>>
>>
>>>Just to be clear, the VIEW in this case is strictly a SELECT * FROM a
>>single
>>>table that has a primary key.
>>>
>>>Regards,
>>>
>>>
>>>Lawson Cronlund
>>>lawson@vrtinc.com
>>>+1(480)308-0641
>>>
>>>-----Original Message-----
>>>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>>>Sent: Monday, January 10, 2011 8:23 AM
>>>To: 'Lawson K. Cronlund'; myodbc@lists.mysql.com
>>>Subject: RE: MS Access/MyODBC Interface Issue
>>>
>>>You've come to the right place.
>>>
>>>Unfortunately, your problem is not related to (or, at least, restricted
>>to)
>>>using views. That's a red herring. If you look around the web you'll see
>>>that
>>>this is a common problem with no clear solution. Some of the suggestions
>>>I've
>>>seen verge on magical thinking.
>>>
>>>I've been working for two weeks to clear this up, and to get rid of the
>>even
>>>
>>>more frightening error 3197. After much horsing around with my application
>>>design, I decided to return to one of my first principles: there is no
>>point
>>>
>>>in arguing with the computer. The computer might not be "right," but it
>>>isn't
>>>going to change its mind.
>>>
>>>One thing I have come to understand, although I can't say that it leads to
>>a
>>>
>>>real solution, is that it is very easy to modify data without thinking
>>about
>>>
>>>what is really going on. Here are some things I've concluded:
>>>
>>>- Unless you are using pass-through queries, you are generally working on
>>a
>>>
>>>local cache of the data.
>>>- It doesn't necessarily mean that someone else edited the data. You,
>>>yourself, are editing data. That dirties the cache, and Access gets
>>>justifiably concerned.
>>>- For example, if you insert (append, in Access terms) a record you will
>>>often not be able to find that record by scrolling back and forth.
>>>- So far as I know your data is always saved even after getting one of
>>the
>>>errors. It scares the heck out of the users, of course.
>>>
>>>I don't know if I'm on the right track or not, but
>>>
>>>- I added a timestamp field to every table that I edit. This is
>>recommended
>>>
>>>all over the web, although it doesn't seem to be a universal solution.
>>>- I have larded my code with .Refresh. In my application, that is causing
>>>performance problem; but my immediate concern is to get the application
>>>working.
>>>- Sometimes I use a pass-through query instead of working with a
>>recordset.
>>>
>>>They are harder to maintain, but I'm used to it from using other things
>>like
>>>
>>>PHP.
>>>- In those cases where I have two forms working on overlapping sets of
>>>data,
>>>I update the other form rather than updating the underlying data.
>>>- If at all possible, test in a multi-user environment. I got some nasty
>>>surprises moving from my test environment to production.
>>>
>>>As I said, I don't know for sure that these techniques will always, or
>>even
>>>often, work; but that's where I am.
>>>
>>>I hope it helps.
>>>
>>>Regards,
>>>
>>>Jerry Schwartz
>>>Global Information Incorporated
>>>195 Farmington Ave.
>>>Farmington, CT 06032
>>>
>>>860.674.8796 / FAX: 860.674.8341
>>>E-mail: jerry@gii.co.jp
>>>Web site: www.the-infoshop.com
>>>
>>>
>>>>-----Original Message-----
>>>>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>>>>Sent: Monday, January 10, 2011 1:45 AM
>>>>To: myodbc@lists.mysql.com
>>>>Subject: MS Access/MyODBC Interface Issue
>>>>
>>>>If this should be posted to another forum, please let me know. I'm
>>posting
>>>>it here because of the interaction with MyODBC/MySQL and the apparent
>>>>difference between a view and a table in primary key identification.
>>>>
>>>>
>>>>
>>>>I am experiencing a peculiar problem and have not been able to find a
>>>>description/solution that matches what I'm seeing.
>>>>
>>>>
>>>>
>>>>The problem:
>>>>
>>>>I have a MS Access 2007 being used as a front end to a MySQL database
>>view
>>>>using ODBC.
>>>>
>>>>
>>>>
>>>>MySQL: MySQL 5.1.49-1ubuintu8.1
>>>>
>>>>ODBC MySQL ODBC 5.1 Driver - Version 5.01.08.00
>>>>
>>>>MS Access 2007 - most recent patches as of 1/9/2011
>>>>
>>>>
>>>>
>>>>The MySQL database has one main table, some supporting table for foreign
>>>key
>>>>validation/control of some column constraints and a number of views that
>>>are
>>>>used to limit the visibility via userid/password login to the main table.
>>>>
>>>>
>>>>
>>>>The main table has a primary key defined (int not bigint) and a timestamp
>>>>field. All time associated fields are defined as datetime. There are no
>>>>bigint columns.
>>>>
>>>>
>>>>
>>>>Each of the normal userids is provided privileges to only one of the
>>views.
>>>>
>>>>
>>>>
>>>>When logged in to the database via MS Access using one of the single view
>>>>only userids, I can make a change to a simple varchar (not constrained)
>>>>field in a row, move off the row successfully, and verify that the change
>>>>was made in the MySQL data table.
>>>>
>>>>
>>>>
>>>>However, if I do the same thing (in MS Access) up to the point of moving
>>>off
>>>>the changed row (presumably committing the change) , and then return to
>>the
>>>>same row and try to undo the change, I get an error from MS Access saying
>>>>"The data has been changed -- Another user edited this record and saved
>>>>the changes before you attempted to save your changes. Re-edit the
>>>record".
>>>>If I click OK and then go to the row in question again, the second time
>>>the
>>>>change can be made.
>>>>
>>>>
>>>>
>>>>Obviously, having an error come up for simple editing is not a good
>>thing.
>>>>
>>>>
>>>>
>>>>This does not happen when accessing the main table directly as a table
>>(not
>>>>through a view). When I set up the linked tables in Access it identifies
>>>>the primary key in the table but needs me to manually specify the primary
>>>>key in the view. It asks for a column that it can use as a unique
>>>>identifier and lists the fields when I choose the objects to link to.
>>>>
>>>>
>>>>
>>>>Is the primary key not identified in a vie or is there something I should
>>>do
>>>>to identify it?
>>>>
>>>>
>>>>
>>>>Thanks in advance for any help you can offer.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>Lawson Cronlund
>>>>
>>>>lawson@vrtinc.com
>>>>
>>>>+1(480)308-0641
>>>>
>>>>+1(602)996-0376 (fax)
>>>>
>>>>Voice Response Technologies, Inc.
>>>>
>>>>5717 E. Justine Rd.
>>>>
>>>>Scottsdale, AZ 85254
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>--
>>>MySQL ODBC Mailing List
>>>For list archives: http://lists.mysql.com/myodbc
>>>To unsubscribe: http://lists.mysql.com/myodbc?unsub=lawson@vrtinc.com
>>
>>
>>
>>
>>
>>--
>>MySQL ODBC Mailing List
>>For list archives: http://lists.mysql.com/myodbc
>>To unsubscribe:
>>http://lists.mysql.com/myodbc?unsub=scaisse@ts.jgh.mcgill. ca
>>
>>
>
>
>
>
>
>--
>MySQL ODBC Mailing List
>For list archives: http://lists.mysql.com/myodbc
>To unsubscribe: http://lists.mysql.com/myodbc?unsub=lawson@vrtinc.com





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

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: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: scaisse@jgh.mcgill.ca [mailto:scaisse@jgh.mcgill.ca]
>Sent: Friday, January 14, 2011 10:43 AM
>To: Lawson K. Cronlund
>Cc: 'Jerry Schwartz'; myodbc@lists.mysql.com
>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"
>To: "'Jerry Schwartz'" ,
>Cc:
>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@vrtinc.com
>+1(480)308-0641





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

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.

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@vrtinc.com
+1(480)308-0641


-----Original Message-----
From: scaisse@jgh.mcgill.ca [mailto:scaisse@jgh.mcgill.ca]
Sent: Friday, January 14, 2011 8:43 AM
To: Lawson K. Cronlund
Cc: 'Jerry Schwartz'; myodbc@lists.mysql.com
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"
To: "'Jerry Schwartz'" ,
Cc:
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@vrtinc.com
+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@vrtinc.com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

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@vrtinc.com
> +1(480)308-0641
>
>
> -----Original Message-----
> From: scaisse@jgh.mcgill.ca [mailto:scaisse@jgh.mcgill.ca]
> Sent: Friday, January 14, 2011 8:43 AM
> To: Lawson K. Cronlund
> Cc: 'Jerry Schwartz'; myodbc@lists.mysql.com
> 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"
> To: "'Jerry Schwartz'",
> Cc:
> 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=gcdmo-myodbc@m.gmane.org

RE: MS Access/MyODBC Interface Issue

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@vrtinc.com
+1(480)308-0641


-----Original Message-----
From: Shawn Green (MySQL) [mailto:shawn.l.green@oracle.com]
Sent: Friday, January 14, 2011 12:38 PM
To: Lawson K. Cronlund
Cc: myodbc@lists.mysql.com
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@vrtinc.com
> +1(480)308-0641
>
>
> -----Original Message-----
> From: scaisse@jgh.mcgill.ca [mailto:scaisse@jgh.mcgill.ca]
> Sent: Friday, January 14, 2011 8:43 AM
> To: Lawson K. Cronlund
> Cc: 'Jerry Schwartz'; myodbc@lists.mysql.com
> 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"
> To: "'Jerry Schwartz'",
> Cc:
> 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@vrtinc.com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: MS Access/MyODBC Interface Issue

>-----Original Message-----
>From: Lawson K. Cronlund [mailto:lawson@vrtinc.com]
>Sent: Friday, January 14, 2011 2:05 PM
>To: myodbc@lists.mysql.com
>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: jerry@gii.co.jp
Web site: www.the-infoshop.com



>Regards,
>
>
>Lawson Cronlund
>lawson@vrtinc.com
>+1(480)308-0641
>
>
>-----Original Message-----
>From: scaisse@jgh.mcgill.ca [mailto:scaisse@jgh.mcgill.ca]
>Sent: Friday, January 14, 2011 8:43 AM
>To: Lawson K. Cronlund
>Cc: 'Jerry Schwartz'; myodbc@lists.mysql.com
>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"
>To: "'Jerry Schwartz'" ,
>Cc:
>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@vrtinc.com
>+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@vrtinc.com
>
>
>--
>MySQL ODBC Mailing List
>For list archives: http://lists.mysql.com/myodbc
>To unsubscribe: http://lists.mysql.com/myodbc?unsub=jerry@gii.co.jp





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org