odbc #deleted

[ellenr]

Obviously no clue here! test table has one varchar(500) default NULL field (named "infofield"), one ID field ("ToDoID") set as not null auto increment (which it does), and one timestamp field ("entrydate"). I can update records and delete records, but each time I add a new record, it shows #DELETED in all fields. If I close the form and reopen it, all is there. This is a test table, pared down to the bare minimum in an effort to solve the malfunctions of a larger table. This db has more than 20 tables with similar design elements that work without a problem. Help!

 

[GinaWhipp]

Versions?

Also, Data Type of fields in table. This might add some insight...
http://support.microsoft.com/kb/128809

 

[ellenr]

Versions: Access 2010, odbc 5.1
"Infofield":mediumtext, NULL:no, default:none
"EntryDate":timestamp, Null:no, default:Current_timestamp
"ToDoID":tinyint(4), Null:no, default:none, auto increment, Primary Key,Unique

I have tried the InfoField as varchar NULL also without success. Nothing quirky is going on in the form--just filling in the blanks. Really scratching my head! Thanks for looking at this.

 

[GinaWhipp]

I wonder if it's the tinyint(4) as a Primary Key, maybe it does not translate well Access. Can you change the to an INT?

 

[KenHigg]

Are these all MS Access tables and not linked db server tables like SQL Server?

 

[ellenr]

Reply to Ken: these are linked tables, linked via odbc to mysql 5.1 db.

 

[ellenr]

changed to INT(4) and still new entries are marked #DELETED

 

[KenHigg]

Have you ever successfully developed with the current set up or is all of this linked table stuff new? There may be db server issues...

 

[GinaWhipp]

Hmm, this seems to be an ongoing problem. Have a look here...
http://bugs.mysql.com/bug.php?id=70851

You might be better off posting the MySQL Bug section...
http://bugs.mysql.com/

 

[KenHigg]

Unicode strikes again?

 

[ellenr]

This is a db that has been running for a number of years with ten locations logging into it. There are approximately thirty tables, all more complicated than this with all sorts of data fields and vba data manipulations, etc. Everything else is working--I am just trying to add a "to do" list for the users and never expected to run into a problem.

 

[KenHigg]

Any chance there has beed an os update since the last real work/ dev on db? Maybe a new windows version?

 

[ellenr]

No new windows version. I am running Win7 and have been since it came out.

 

[GinaWhipp]

Could you gleam anything off the link I posted?

 

[ellenr]

No, not really. I had been there before. I deleted the mysql table, set it up locally and uploaded it. I thought I had it working--I opened the table and tried loading data there. I had added a yes/no field (as an INT(4)). When I added data, I got the #DELETED in all fields unless I entered a zero in the Y/N field. Went back to the mysql db to be sure the default was 0 there--it was, but didn't show up in the new record. So, I went back in and just deleted that field. Now, when I go into the table view and add a record, all fields still show #DEL unless I fill in the one other field manually. That field set as a date/time field. It didn't require me to fill in that field before I removed the Y/N field. I am quitting for the night! Maybe a fresher mind will help tomorrow. Thank you for your ideas--if I ever figure out the secret I will post it. If you have any inspiration for me, please let me know.

 

[ellenr]

Here are my settings (attachment). Still not working.

 

[ellenr]

Finally! I changed the memo field to varchar(200) and suddenly all was ok. Thanks to all of you who helped me think.

 

[GinaWhipp]

Thank you for posting back with what worked! Bound to help some other person from going bald...

 

[KenHigg]

Glad you figured it out!