> -----Original Message----- > From: Daniel Kasak [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 04, 2006 5:45 PM > To: C K; mysql@lists.mysql.com > Subject: Re: MS Access gives error no. -7776. > > C K wrote: > > > Thanks for your sugesstions. I tried to search this issue on > > microsoft's website. and found that this problem is related with > > timestamp fields. I have tried to connect from access 2k and 2003 to > > mysql database. It works well for all the things. but gives above > > error only when control jumps to subform with diff. table as it's > > recordsource. Strange thing is that on few PCs it gives error and on > > some it not gives any error. I have installed WinXP with SP2, Access > > 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL > > server 2005 express edition > > I am tring to use seperate forms for dataentry. Thanks again > > CPK > > On 7/3/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: > > Contrary to the advice given on working with MS Access, I've found that > I can *only* get things to work with Access 2003 if I remove the > timestamp field. > Also, make sure your primary key column isn't larger than an int ( ie > don't use int unsigned, and don't use bigint ).
I recently converted an application with 1M+ records to use mysql as the backend. I had similar errors until I: Make sure every table haa a unique key field Convert all timestamp fields to datetime (via alter table) Add one (and only one) last_changed timestamp, and fill it with a valid stamp Null out datetime fields that have '0000-00-00 00:00:00' as their value. Refresh the ODBC links. Access likes to use the primary key field + the last_changed timestamp in it's update queries so that it can easily manage optimistic locking. It issues update queries like Update some_table ... where primary_key = 27 and last_changed = '2006-12-01 23:52:55'. If the query doesn't affect any rows, then Access knows that someone else changed the record since it was initially retrieved, and can then prompt the user accordingly. Access also throws errors when it finds that timestamp or datetime fields have '0000-00-00 00:00:00'. Immediately after I loaded the mysql tables with the access data, I ran an 'update $full_table set $full_column = NULL where $full_column = '0000-00-00 00:00:00' on every datetime column in the database. In the case of the special last_changed column, I set all records to '1980-01-01 00:00:00' immediately following the conversion. Since this column was added by the conversion, it is never referenced in any forms, which seems to help things. The bad news is that all of the automatic date fields that were in the original .mdb file now have to be filled in with 'now()' in all sorts of places like forms and insert queries. I also had some trouble where a data entry subform would not allow additions. The 'add record' part of the record navigator would be grayed out. Usually, this was because I had neglected to have a primary key + last_changed timestamp on the table in question. In some cases, it was because a data source for a subform was a query. What I did to get those to work was to change the datasource to the table, and supply a proper filter/link. That aside, I feel much better having the data backed by mysql. The application has now been running successfully for about three weeks and users do not notice any difference in performance. Our regular backup capability now encompasses backup and restore for the data, which is great for us. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
