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" <[email protected]>
To:	<[email protected]>, <[email protected]>
Date:	2011/01/05 11:08
Subject:	RE: Error 3197



>-----Original Message-----
>From: Oladapo Carew [mailto:[email protected]]
>Sent: Wednesday, January 05, 2011 10:43 AM
>To: 'Jerry Schwartz'; [email protected]
>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:[email protected]]
>Sent: Wednesday, January 05, 2011 5:35 PM
>To: [email protected]; [email protected]
>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: [email protected]
>Web site: www.the-infoshop.com
>
>
>>-----Original Message-----
>>From: Oladapo Carew [mailto:[email protected]]
>>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:[email protected]]
>>Sent: Wednesday, January 05, 2011 12:22 AM
>>To: [email protected]
>>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:  <mailto:[email protected]> [email protected]
>>
>>Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>>
>>
>>
>
>





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




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