It's link is- http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp
CPK ************* On 7/6/06, C K <[EMAIL PROTECTED]> wrote:
Today I also carried out some thing same as you. I already have Primary Keys in all tables. Then I added a temporary timestamp field with default as '1999-11-11 11:11:11'. Then I updated all such columns with above value. There is no 'on_update current timestamp' settings, just defined default value. Then I relinked all tables and now it is working ok. Now I will try it on other open source databases. Note that there is no foreign key defined yet. It is working OK on subforms also. Only one thing is that it is giving error for 'Save Record' action now. I also got jet 3.0 manual from microsoft's site. There are clear definations for such errors. But this page is out dated and I could not find any manual for jet 4.0.Please check it. I will try to send it's link in next email. Regards, CPK On 7/6/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: > > -----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] > >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
