Error 3197

------=_NextPart_000_0070_01CBAC33.ED1AB090
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Iâ€=99ve moved an Access 2007 application over to MySQL, using the =
ODBC connector 5.1. When I try to update a record, as often as not =
Iâ€=99ll get a 3197. Sometimes if I move back and forth in the table =
I can get around it, but I canâ€=99t ask my users to go through =
various gyrations until they stop getting nasty messages.

=20

Iâ€=99ve searched the web, and all Iâ€=99ve found are complaints =
and suggestions that didnâ€=99t work for me.

=20

Iâ€=99ve tried toggling=20

=20

· dynamic cursors

· return matched rows

=20

with no luck.

=20

In Access, all of the forms are designated to use Dynasets =
(inconsistent).

=20

Any suggestions? My job is more or less on the line.

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp=20

Web site: www.the-infoshop.com

=20


------=_NextPart_000_0070_01CBAC33.ED1AB090--

RE: Error 3197

A lot of work, but...

Unbind the form, use classes, and do your writes through code.


Mike Nelson
Seattle, WA
website: http://www.feman.com
blog: http://thefeman.blogspot.com


-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii.co.jp]
Sent: Tuesday, January 04, 2011 2:22 PM
To: myodbc@lists.mysql.com
Subject: Error 3197

I've moved an Access 2007 application over to MySQL, using the ODBC
connector 5.1. When I try to update a record, as often as not I'll get a
3197. Sometimes if I move back and forth in the table I can get around it,
but I can't ask my users to go through various gyrations until they stop
getting nasty messages.



I've searched the web, and all I've found are complaints and suggestions
that didn't work for me.



I've tried toggling



.. dynamic cursors

.. return matched rows



with no luck.



In Access, all of the forms are designated to use Dynasets (inconsistent).



Any suggestions? My job is more or less on the line.



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





--
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: Error 3197

>-----Original Message-----
>From: Mike Nelson [mailto:mikenelson@seanet.com]
>Sent: Tuesday, January 04, 2011 8:15 PM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>Subject: RE: Error 3197
>
>A lot of work, but...
>
>Unbind the form, use classes, and do your writes through code.
>
[JS] Thanks -- I guess.

Actually, I'm running into the same error using just a tiny bit of code.

-----
WKSql = "SELECT [TA_ID_Management].[ID number Lastly Taken] FROM
TA_ID_Management WHERE (([TA_ID_Management].[ID Management KEY])=""" & WKKey &
""");"
Set myDB = CurrentDb
Set Myqdf = myDB.CreateQueryDef("")
Myqdf.SQL = WKSql
Set Myset = Myqdf.OpenRecordset(dbOpenDynaset)
Do Until Myset.EOF
Myset.Edit
Myset![ID number Lastly Taken] = WKMaxID
Myset.Update
Myset.MoveNext
WKOk = True
Loop
Myset.Close
myDB.Close
-----

Would using classes fix the problem? That seems bizarre.


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




>
>Mike Nelson
>Seattle, WA
>website: http://www.feman.com
>blog: http://thefeman.blogspot.com
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Tuesday, January 04, 2011 2:22 PM
>To: myodbc@lists.mysql.com
>Subject: Error 3197
>
>I've moved an Access 2007 application over to MySQL, using the ODBC
>connector 5.1. When I try to update a record, as often as not I'll get a
>3197. Sometimes if I move back and forth in the table I can get around it,
>but I can't ask my users to go through various gyrations until they stop
>getting nasty messages.
>
>
>
>I've searched the web, and all I've found are complaints and suggestions
>that didn't work for me.
>
>
>
>I've tried toggling
>
>
>
>. dynamic cursors
>
>. return matched rows
>
>
>
>with no luck.
>
>
>
>In Access, all of the forms are designated to use Dynasets (inconsistent).
>
>
>
>Any suggestions? My job is more or less on the line.
>
>
>
>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
>
>
>





--
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: Error 3197

Hi Jerry,

The consensus on the forums seems to be to replace the native DAO / ADO =
Update methods with SQL Executes.

e.g.
rs.Open
rs.FindFirst "ID =3D 24601"
rs!TastesLike =3D "chicken"
rs.Update
rs.Close

becomes
con.Execute "UPDATE animaltable SET TastesLike=3D'chicken' WHERE ID =3D =
24601"

I appreciate that this isn't always a simple rewrite, and I don't know =
whether it's practical for your application, but it seems to have had =
more success than other suggestions.

See also: http://forums.mysql.com/read.php?65,44969,44969#msg-44969

Kind regards,

Al
Integritec Limited


-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii.co.jp]=20
Sent: 04 January 2011 22:22
To: myodbc@lists.mysql.com
Subject: Error 3197

Iâ€=99ve moved an Access 2007 application over to MySQL, using the =
ODBC connector 5.1. When I try to update a record, as often as not =
Iâ€=99ll get a 3197. Sometimes if I move back and forth in the table =
I can get around it, but I canâ€=99t ask my users to go through =
various gyrations until they stop getting nasty messages.

=20

Iâ€=99ve searched the web, and all Iâ€=99ve found are complaints =
and suggestions that didnâ€=99t work for me.

=20

Iâ€=99ve tried toggling=20

=20

· dynamic cursors

· return matched rows

=20

with no luck.

=20

In Access, all of the forms are designated to use Dynasets =
(inconsistent).

=20

Any suggestions? My job is more or less on the line.

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp=20

Web site: www.the-infoshop.com

=20



--
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: Error 3197

To be honest, last night I was floundering around trying to handle a lot of
different problems, including wildly different behavior between my test
environment and my production environments.

I've going to add timestamps today and see what happens. There's also a
tick-list on the MySQL web site, although some of that sounds like it is trial
and error.

Thanks.

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: Oladapo Carew [mailto:dapo.carew@caresoft.co.mz]
>Sent: Wednesday, January 05, 2011 3:05 AM
>To: 'Jerry Schwartz'
>Subject: RE: Error 3197
>
>Hi Jerry,
>
>Make sure you have a timestamp field in your tables. Also, are you using
>.edit/updates? If so, why not use sql statements??
>
>Regards,
>Dapo
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Wednesday, January 05, 2011 12:22 AM
>To: myodbc@lists.mysql.com
>Subject: Error 3197
>
>I've moved an Access 2007 application over to MySQL, using the ODBC connector
>5.1. When I try to update a record, as often as not I'll get a 3197.
>Sometimes
>if I move back and forth in the table I can get around it, but I can't ask my
>users to go through various gyrations until they stop getting nasty messages.
>
>
>
>I've searched the web, and all I've found are complaints and suggestions that
>didn't work for me.
>
>
>
>I've tried toggling
>
>
>
>· dynamic cursors
>
>· return matched rows
>
>
>
>with no luck.
>
>
>
>In Access, all of the forms are designated to use Dynasets (inconsistent).
>
>
>
>Any suggestions? My job is more or less on the line.
>
>
>
>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
>
>
>





--
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: Error 3197

Well, that's ONE consensus. :-(

Another seems to be to add timestamps to all of the tables.

The problem with using SQL is that most of the forms don't use explicit code
at all: they simply do what Access forms do.

I have to think about which event(s) to trap and which ones to cancel.

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: Al McNicoll [mailto:al@integritec.co.uk]
>Sent: Wednesday, January 05, 2011 5:06 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>Subject: RE: Error 3197
>
>Hi Jerry,
>
>The consensus on the forums seems to be to replace the native DAO / ADO
>Update
>methods with SQL Executes.
>
>e.g.
>rs.Open
>rs.FindFirst "ID = 24601"
>rs!TastesLike = "chicken"
>rs.Update
>rs.Close
>
>becomes
>con.Execute "UPDATE animaltable SET TastesLike='chicken' WHERE ID = 24601"
>
>I appreciate that this isn't always a simple rewrite, and I don't know
>whether
>it's practical for your application, but it seems to have had more success
>than
>other suggestions.
>
>See also: http://forums.mysql.com/read.php?65,44969,44969#msg-44969
>
>Kind regards,
>
>Al
>Integritec Limited
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: 04 January 2011 22:22
>To: myodbc@lists.mysql.com
>Subject: Error 3197
>
>I've moved an Access 2007 application over to MySQL, using the ODBC connector
>5.1. When I try to update a record, as often as not I'll get a 3197.
>Sometimes
>if I move back and forth in the table I can get around it, but I can't ask my
>users to go through various gyrations until they stop getting nasty messages.
>
>
>
>I've searched the web, and all I've found are complaints and suggestions that
>didn't work for me.
>
>
>
>I've tried toggling
>
>
>
>· dynamic cursors
>
>· return matched rows
>
>
>
>with no luck.
>
>
>
>In Access, all of the forms are designated to use Dynasets (inconsistent).
>
>
>
>Any suggestions? My job is more or less on the line.
>
>
>
>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
>
>
>





--
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: Error 3197

Hi Jerry,

As seen from the other posts, you might have to do a bit more than that. You
might have to also make use of sql statements instead of the ms access
..edit/update routines.

Best Regards,
Dapo

-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii.co.jp]
Sent: Wednesday, January 05, 2011 5:35 PM
To: dapo.carew@caresoft.co.mz; myodbc@lists.mysql.com
Subject: RE: Error 3197

To be honest, last night I was floundering around trying to handle a lot of
different problems, including wildly different behavior between my test
environment and my production environments.

I've going to add timestamps today and see what happens. There's also a
tick-list on the MySQL web site, although some of that sounds like it is
trial and error.

Thanks.

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: Oladapo Carew [mailto:dapo.carew@caresoft.co.mz]
>Sent: Wednesday, January 05, 2011 3:05 AM
>To: 'Jerry Schwartz'
>Subject: RE: Error 3197
>
>Hi Jerry,
>
>Make sure you have a timestamp field in your tables. Also, are you
>using .edit/updates? If so, why not use sql statements??
>
>Regards,
>Dapo
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Wednesday, January 05, 2011 12:22 AM
>To: myodbc@lists.mysql.com
>Subject: Error 3197
>
>I've moved an Access 2007 application over to MySQL, using the ODBC
>connector 5.1. When I try to update a record, as often as not I'll get a
3197.
>Sometimes
>if I move back and forth in the table I can get around it, but I can't
>ask my users to go through various gyrations until they stop getting nasty
messages.
>
>
>
>I've searched the web, and all I've found are complaints and
>suggestions that didn't work for me.
>
>
>
>I've tried toggling
>
>
>
>. dynamic cursors
>
>. return matched rows
>
>
>
>with no luck.
>
>
>
>In Access, all of the forms are designated to use Dynasets (inconsistent).
>
>
>
>Any suggestions? My job is more or less on the line.
>
>
>
>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
>
>
>




--
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: Error 3197

>-----Original Message-----
>From: Oladapo Carew [mailto:dapo.carew@caresoft.co.mz]
>Sent: Wednesday, January 05, 2011 10:43 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>Subject: RE: Error 3197
>
>Hi Jerry,
>
>As seen from the other posts, you might have to do a bit more than that. You
>might have to also make use of sql statements instead of the ms access
>.edit/update routines.
>
[JS] Actually, now that I've had a few cups of coffee I remember that I tried
that .execute in a simple test case.

The problem was that I need to verify that a change was actually made, and
..RecordsAffected will indeed give me the number of records changed; but I need
to know if there were no qualifying records, and . RecordsAffected can't
distinguish between "found no records" and "changed no records."

If you have any ideas about that, let me know. I wasn't able to get the
combination of a "SELECT" and an update to work. I will try again with a
clearer head.

That still leaves the problem of forms that don't use any code. I think what I
have to do is put my queries into the BeforeUpdate event, and then cancel the
BeforeUpdate event, and then trigger the "AfterUpdate" event; but I haven't
thought that through.



>Best Regards,
>Dapo
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Wednesday, January 05, 2011 5:35 PM
>To: dapo.carew@caresoft.co.mz; myodbc@lists.mysql.com
>Subject: RE: Error 3197
>
>To be honest, last night I was floundering around trying to handle a lot of
>different problems, including wildly different behavior between my test
>environment and my production environments.
>
>I've going to add timestamps today and see what happens. There's also a
>tick-list on the MySQL web site, although some of that sounds like it is
>trial and error.
>
>Thanks.
>
>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: Oladapo Carew [mailto:dapo.carew@caresoft.co.mz]
>>Sent: Wednesday, January 05, 2011 3:05 AM
>>To: 'Jerry Schwartz'
>>Subject: RE: Error 3197
>>
>>Hi Jerry,
>>
>>Make sure you have a timestamp field in your tables. Also, are you
>>using .edit/updates? If so, why not use sql statements??
>>
>>Regards,
>>Dapo
>>
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>>Sent: Wednesday, January 05, 2011 12:22 AM
>>To: myodbc@lists.mysql.com
>>Subject: Error 3197
>>
>>I've moved an Access 2007 application over to MySQL, using the ODBC
>>connector 5.1. When I try to update a record, as often as not I'll get a
>3197.
>>Sometimes
>>if I move back and forth in the table I can get around it, but I can't
>>ask my users to go through various gyrations until they stop getting nasty
>messages.
>>
>>
>>
>>I've searched the web, and all I've found are complaints and
>>suggestions that didn't work for me.
>>
>>
>>
>>I've tried toggling
>>
>>
>>
>>. dynamic cursors
>>
>>. return matched rows
>>
>>
>>
>>with no luck.
>>
>>
>>
>>In Access, all of the forms are designated to use Dynasets (inconsistent).
>>
>>
>>
>>Any suggestions? My job is more or less on the line.
>>
>>
>>
>>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
>>
>>
>>
>
>





--
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: Error 3197

Jerry,

I had very similar problems several years ago. Adding timestamps on all of =
the tables resolved the issue for me. The MySQL version we used is an older=
version. And, it is running through ODBC 5.1 as well.

Good Luck!

Ron


-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii.co.jp]=20
Sent: Wednesday, January 05, 2011 10:35 AM
To: 'Al McNicoll'; myodbc@lists.mysql.com
Subject: RE: Error 3197

Well, that's ONE consensus. :-(

Another seems to be to add timestamps to all of the tables.

The problem with using SQL is that most of the forms don't use explicit cod=
e=20
at all: they simply do what Access forms do.

I have to think about which event(s) to trap and which ones to cancel.

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: Al McNicoll [mailto:al@integritec.co.uk]
>Sent: Wednesday, January 05, 2011 5:06 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>Subject: RE: Error 3197
>
>Hi Jerry,
>
>The consensus on the forums seems to be to replace the native DAO / ADO=20
>Update
>methods with SQL Executes.
>
>e.g.
>rs.Open
>rs.FindFirst "ID =3D 24601"
>rs!TastesLike =3D "chicken"
>rs.Update
>rs.Close
>
>becomes
>con.Execute "UPDATE animaltable SET TastesLike=3D'chicken' WHERE ID =3D 24=
601"
>
>I appreciate that this isn't always a simple rewrite, and I don't know=20
>whether
>it's practical for your application, but it seems to have had more success=
=20
>than
>other suggestions.
>
>See also: http://forums.mysql.com/read.php?65,44969,44969#msg-44969
>
>Kind regards,
>
>Al
>Integritec Limited
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: 04 January 2011 22:22
>To: myodbc@lists.mysql.com
>Subject: Error 3197
>
>I've moved an Access 2007 application over to MySQL, using the ODBC connec=
tor
>5.1. When I try to update a record, as often as not I'll get a 3197.=20
>Sometimes
>if I move back and forth in the table I can get around it, but I can't ask=
my
>users to go through various gyrations until they stop getting nasty messag=
es.
>
>
>
>I've searched the web, and all I've found are complaints and suggestions t=
hat
>didn't work for me.
>
>
>
>I've tried toggling
>
>
>
>* dynamic cursors
>
>* return matched rows
>
>
>
>with no luck.
>
>
>
>In Access, all of the forms are designated to use Dynasets (inconsistent).
>
>
>
>Any suggestions? My job is more or less on the line.
>
>
>
>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
>
>
>





--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dralexander@csmrese=
arch.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: Error 3197

I'm still dealing with such issues in Access. Often the TIMESTAMP doesn't
help at all. Obviously this is one of the first steps
After :
* Making sure that you have a "understandable" PK set,
* You have the latest ODBC driver
* with the proper options selected (everything is in the MySQL
Documentation on the website)
* You have the latest Access Services Pack and Hotfixes.
* You tried to relink the table,
* then you tried to relink the table from scratch: delete it first then
link it again... this rarely works though, so if you have lookups
programmed, save a copy of your Access project first and if it doesn't
work, revert to that version instead of reprogramming the lookups...
* I also make sure I have the latest MySQL Community Edition.

Remember that when you add a TIMESTAMP, you actually have to populate them
with values (they don't need to be unique, though that might help too); if
you leave them at 0/NULL, Access most probably won't like that (unless the
table is empty obviously).

Another tip would be to try and avoid MySQL "0" DATEs, I found that this is
often the cause of compatibility issues even though the ODBC drivers tries
to hide it. I would try to use NULLable DATEs instead. Personally I try to
avoid "empty" DATEs altogether.

I sometimes have various results depending on the Access version (my users
have XP, 2003, 2007 and 2010). I develop in 2010 and test in 2003 in a
virtual machine. It *usually* works better with 2010 so that could be
something to try to do and get the client desktops to upgrade to 2010.

With all that crap, most of the time it still doesn't work, so I end up
using CurrentDb.Execute Query, dbSqlPassThrough.

Note the dbSqlPassThrough. Though I first try without, usualy I end up
needing it (since without it it's still an normal ODBC call).

And even with this, I STILL have some issues, specially with the client
locale vs the server locale, mostly regarding dates, decimal/list
seperators and string escaping...

- Sebastien Caisse



From: "Jerry Schwartz"
To: ,
Date: 2011/01/05 11:08
Subject: RE: Error 3197



>-----Original Message-----
>From: Oladapo Carew [mailto:dapo.carew@caresoft.co.mz]
>Sent: Wednesday, January 05, 2011 10:43 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>Subject: RE: Error 3197
>
>Hi Jerry,
>
>As seen from the other posts, you might have to do a bit more than that.
You
>might have to also make use of sql statements instead of the ms access
>.edit/update routines.
>
[JS] Actually, now that I've had a few cups of coffee I remember that I
tried
that .execute in a simple test case.

The problem was that I need to verify that a change was actually made, and
..RecordsAffected will indeed give me the number of records changed; but I
need
to know if there were no qualifying records, and . RecordsAffected can't
distinguish between "found no records" and "changed no records."

If you have any ideas about that, let me know. I wasn't able to get the
combination of a "SELECT" and an update to work. I will try again with a
clearer head.

That still leaves the problem of forms that don't use any code. I think
what I
have to do is put my queries into the BeforeUpdate event, and then cancel
the
BeforeUpdate event, and then trigger the "AfterUpdate" event; but I haven't

thought that through.



>Best Regards,
>Dapo
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Wednesday, January 05, 2011 5:35 PM
>To: dapo.carew@caresoft.co.mz; myodbc@lists.mysql.com
>Subject: RE: Error 3197
>
>To be honest, last night I was floundering around trying to handle a lot
of
>different problems, including wildly different behavior between my test
>environment and my production environments.
>
>I've going to add timestamps today and see what happens. There's also a
>tick-list on the MySQL web site, although some of that sounds like it is
>trial and error.
>
>Thanks.
>
>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: Oladapo Carew [mailto:dapo.carew@caresoft.co.mz]
>>Sent: Wednesday, January 05, 2011 3:05 AM
>>To: 'Jerry Schwartz'
>>Subject: RE: Error 3197
>>
>>Hi Jerry,
>>
>>Make sure you have a timestamp field in your tables. Also, are you
>>using .edit/updates? If so, why not use sql statements??
>>
>>Regards,
>>Dapo
>>
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>>Sent: Wednesday, January 05, 2011 12:22 AM
>>To: myodbc@lists.mysql.com
>>Subject: Error 3197
>>
>>I've moved an Access 2007 application over to MySQL, using the ODBC
>>connector 5.1. When I try to update a record, as often as not I'll get a
>3197.
>>Sometimes
>>if I move back and forth in the table I can get around it, but I can't
>>ask my users to go through various gyrations until they stop getting
nasty
>messages.
>>
>>
>>
>>I've searched the web, and all I've found are complaints and
>>suggestions that didn't work for me.
>>
>>
>>
>>I've tried toggling
>>
>>
>>
>>. dynamic cursors
>>
>>. return matched rows
>>
>>
>>
>>with no luck.
>>
>>
>>
>>In Access, all of the forms are designated to use Dynasets
(inconsistent).
>>
>>
>>
>>Any suggestions? My job is more or less on the line.
>>
>>
>>
>>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
>>
>>
>>
>
>





--
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=gcdmo-myodbc@m.gmane.org

RE: Error 3197

Alas, that didn't work.

I am resorting to a number of different tricks, as described in another
message.

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: Al McNicoll [mailto:al@integritec.co.uk]
>Sent: Wednesday, January 05, 2011 5:06 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>Subject: RE: Error 3197
>
>Hi Jerry,
>
>The consensus on the forums seems to be to replace the native DAO / ADO
>Update
>methods with SQL Executes.
>
>e.g.
>rs.Open
>rs.FindFirst "ID = 24601"
>rs!TastesLike = "chicken"
>rs.Update
>rs.Close
>
>becomes
>con.Execute "UPDATE animaltable SET TastesLike='chicken' WHERE ID = 24601"
>
>I appreciate that this isn't always a simple rewrite, and I don't know
>whether
>it's practical for your application, but it seems to have had more success
>than
>other suggestions.
>
>See also: http://forums.mysql.com/read.php?65,44969,44969#msg-44969
>
>Kind regards,
>
>Al
>Integritec Limited
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: 04 January 2011 22:22
>To: myodbc@lists.mysql.com
>Subject: Error 3197
>
>I've moved an Access 2007 application over to MySQL, using the ODBC connector
>5.1. When I try to update a record, as often as not I'll get a 3197.
>Sometimes
>if I move back and forth in the table I can get around it, but I can't ask my
>users to go through various gyrations until they stop getting nasty messages.
>
>
>
>I've searched the web, and all I've found are complaints and suggestions that
>didn't work for me.
>
>
>
>I've tried toggling
>
>
>
>· dynamic cursors
>
>· return matched rows
>
>
>
>with no luck.
>
>
>
>In Access, all of the forms are designated to use Dynasets (inconsistent).
>
>
>
>Any suggestions? My job is more or less on the line.
>
>
>
>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
>
>
>





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