Re: MS Access gives error no. -7776.
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 '-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 '-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 = '-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]
Re: MS Access gives error no. -7776.
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 '-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 '-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 = '-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]
RE: MS Access gives error no. -7776.
-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 '-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 '-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 = '-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]
Re: MS Access gives error no. -7776.
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: d -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Sunday, July 02, 2006 8:29 AM To: mysql@lists.mysql.com Subject: MS Access gives error no. -7776. Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Hello, I recently converted a good size (1M records) access database to use MySQL as the backend. I had to do a number of conversion steps. All tables must have a primary key. After the tables were exported, I checked them to see that they did with a perl script. If they did not, I added a field called primary_key not null auto_increment and made it a primary key. I also had trouble getting forms to allow me to add records. I found that having the data source be a query, rather than a table (possibly with a filter) was the cause. All tables must have one and only one timestamp field. This was the worst issue. Access wants one timestamp field that auto updates so that it can do it's optimistic locking routine. I had to write a script to go through my database and convert all timestamp fields to datetime fields. Then, I had to go back and add a last_changed timestamp field to every table. The big issue here is that timestamp fields cannot be set to default to CURRENT_TIME or now(). I had to work through all of the forms and queries in the database and explicitly set them to now() as needed. Be careful of bit fields. In general, the conversion tool I used to initially populate the MySQL tables (sqlYog) seems to want to set bit fields to unsigned. This is bad for access, as it uses -1 for true. Make sure your bit fields get set to unsigned in the Mysql table definition. The other thing I've noticed is that datatype mappings aren't necessarily intuitive. I think currency fields wind up something like 19/4 in the mysql table. I tried changing their definition to something like 9/2, but then access starts throwing errors. Lastly, the original creator of our database did not set up any relationships. I had to manually add all of the foreign key constraints, and in order to do that I had to clean up the child tables such that there weren't any records that had missing parent records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MS Access gives error no. -7776.
On 7/4/06, C K [EMAIL PROTECTED] 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: d -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Sunday, July 02, 2006 8:29 AM To: mysql@lists.mysql.com Subject: MS Access gives error no. -7776. Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Hello, I recently converted a good size (1M records) access database to use MySQL as the backend. I had to do a number of conversion steps. All tables must have a primary key. After the tables were exported, I checked them to see that they did with a perl script. If they did not, I added a field called primary_key not null auto_increment and made it a primary key. I also had trouble getting forms to allow me to add records. I found that having the data source be a query, rather than a table (possibly with a filter) was the cause. All tables must have one and only one timestamp field. This was the worst issue. Access wants one timestamp field that auto updates so that it can do it's optimistic locking routine. I had to write a script to go through my database and convert all timestamp fields to datetime fields. Then, I had to go back and add a last_changed timestamp field to every table. The big issue here is that timestamp fields cannot be set to default to CURRENT_TIME or now(). I had to work through all of the forms and queries in the database and explicitly set them to now() as needed. Be careful of bit fields. In general, the conversion tool I used to initially populate the MySQL tables (sqlYog) seems to want to set bit fields to unsigned. This is bad for access, as it uses -1 for true. Make sure your bit fields get set to unsigned in the Mysql table definition. The other thing I've noticed is that datatype mappings aren't necessarily intuitive. I think currency fields wind up something like 19/4 in the mysql table. I tried changing their definition to something like 9/2, but then access starts throwing errors. Lastly, the original creator of our database did not set up any relationships. I had to manually add all of the foreign key constraints, and in order to do that I had to clean up the child tables such that there weren't any records that had missing parent records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MS Access gives error no. -7776.
Hi CPK, I suppose you've looked at the manual in: http://dev.mysql.com/doc/refman/5.0/en/myodbc-examples.html It you still have problems, try just the linked table, without forms. If you can view the linked MySQL tables in MS Access, then the problem has to do with the forms, and not with the connection. HTH, Cor - Original Message - From: C K [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, July 02, 2006 2:29 PM Subject: MS Access gives error no. -7776. Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- 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]
Re: MS Access gives error no. -7776.
C K wrote: Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. If it's urgent, you should consider posting more information, or you will loose time while people ask you to send more information, for example: - output of 'show create table xx' for each table - version of Access, including service packs - version of Windows -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
