| Bug #59968 | MS Access ODBC Driver 5.1.8 #DELETED on Insert after Changing field to NULL | ||
|---|---|---|---|
| Submitted: | 5 Feb 2011 20:35 | Modified: | 29 May 2013 12:53 |
| Reporter: | Tim Gold | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.1.8 | OS: | Any |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
| Tags: | ODBC 5.1.8 Access #DELETED on insert record | ||
[17 Jun 2011 7:53]
Bogdan Degtyariov
Tim, I could not repeat the problem in Access 2007 and 2010. Both times NULL values assigned correctly to the columns. Here is the table I used: tabnull | CREATE TABLE `tabnull` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vcf` varchar(45) DEFAULT NULL, `dtf` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
[17 Jul 2011 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[29 May 2013 12:53]
Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.
Description: I have been developing a MS Access 2010 Application using MySQL 5.1 as the backend. I have about 30 Linked tables and have had extremely good success with it until a problem presented itself that I finally tracked to a specific action. My connection strings are built according to all the recommendations and using Option=3 in the string to make sure FLAG_FOUND_ROWS are implemented. I know in earlier version this has been a problem because MS Jet engine loses Cursor position under some circumstances. The problem arises when a new record is started and all the NOT NULL fields are added but before the insert is complete a Default Null field is changed from NULL and then back to NULL again. The record does get added with all the NOT NULL fields but shows up ad #DELETED. I tried to add additional Flags ie. FLAG_DYNAMIC_CURSOR to work around the issue but no matter what I try the same thing happens. If I follow through will all the initial changes and don't ever set the "Default NULL" fields back to NULL even if I change them to "" then the record succeeds and the cursor follows fine. Are there any recommendations? How to repeat: Create a linked table to MS ACCESS with all the recommended field ie. ID - Primary Key and some NOT NULL Fields and some Default NULL fields including at TIME_STAMP field. Connection String -"Driver={MySQL ODBC 5.1 Driver};Server=IP;Port=3306;Database=DB;User=User;Password=Pass;Option=3" The issue was tested with default NULL VARCHAR() and DATETIME fields being changed in a new record from NULL to valid value then back to NULL.