problem with mysql auto increment column and MS Access

[kingraoul]

Hi, first of all, i'm new to the forum and maybe this question has been answered, i did some searching but find nothing useful. Well, the problem is that i use mysql with MS Access and when i save a record that has an auto increment column (in this case is the primary key) MS access marks the record as #eliminated# and i must requery the recordset to show the new entry. I did a lot of testing and got to nothing, someone has an idea of what is happening? Maybe it's normal, but i could not find informations about such a behaviour.
Thanks.

 

[QatQat]

It happens very often with My SQL for Win. That does not stop the record from being inserted.

Are you using the WIn version?


Bye


Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[kingraoul]

Thank you for your answer. Yes, i use the last production version for Win. I know that the record is being inserted, but i built an application in MS Access that relies on the primary key being known while editing the record (or at least after having saved it). If the record disappears i must requery and order the data on Primary Key DESC and get the first record. Not a huge problem, but i don't think it's normal.

Bye.

Kingraoul

 

[QatQat]

TI had the same behavior but fortunately I don't have to use MS Acces.
I can tell you, if it helps you, that if you use your MyODBC driver to connect to the Linux version of it it does not do it.

A suggestion, try use older version of MyODBC if you are using vers 3.5 Beta

Unfortunately that behavior happens randomly and I can't give you more detailed information as I could not fault anything specific; sometimes it happens, sometime it does not...
If you can try using the linux version


Bye


Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[kingraoul]

Thank you for your answer, but i tried myodbc 3.51 and 2.50 and i had the same problem, so i was nearly sure that the ODBC driver wasn't the cause. (blame on Microsoft as always, i thought). I think i'll wait for another MyODBC version, or maybe i'll tune my app so it requeries every time a new record is inserted , unfortunately, i don't use Linux right now.

Bye

Max

 

[kingraoul]

I found something interesting in the microsoft knowledge base and in the MySQL manual. They both advice to update the jet components of office. I got the update but nothing helpful happened. This should have fixed a problem of #deleted record if a uniqueidentifier was part of the record. Well, i found instead that if i insert manually a 0 in the autoincrement column, when the record is saved it magically turns in the correct auto-incremented value! If i leave the field blank it marks the whole redord as #deleted. I wonder if this works from the code inside the forms. Tomorrow i'll try. Hope it's useful to someone else. And i hope to get a final solution to this problem. :-( sorry to bother you all.

Bye

Kingraoul

 

[QatQat]

Indeed it works Kingraoul,

I will do some more tests and perhaps I will understand more about this.

Bye

Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[JustKIDn]

Hey!

Don't leave me hanging here guys!

I've searched the Forum and this is the only thread that speaks of the #Deleted records.

I used Access 97 and linked to a table in MySQL.
I then copied a group of records from an existing Access table and pasted them into the linked table with same structure.

Suddenly, all the fields that should have values (currently, some are Null and should be) say #Deleted.

I checked MySQL and the records are all right (showing correct values).
I added a record with MySQL (without Access) and although it shows correctly in MySQL. Using Access, even the new record shows up with #Deleted in all of the fields with values.

But if I do a query in Access. The query works right, but the values still all show #Deleted.

It sounds like you guys discovered a solution. So spill it guys. Share the (knowledge) wealth!

I'm thinking of using Access or Visual Basic as the frontend for my database programs.

Any new info on this yet?

Thanks in advance.
tgus

____________________________
Families can be together forever...

 

[QatQat]

Hi tgus,

I have overcome the problem using MySQL 4.0.5 Beta and upgrading MyODBC to vers 2.50.39

This did it for me. Don't ask me why.


Bye


Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[JustKIDn]

Thanks QatQat,

I only had a small sample DB I had started. So I uninstalled MySQL (and deleted the DB) and reinstalled 4.05.12.
Also I have MyODBC version 3.51.06.

Are you sure that 'vers 2.50.39' is an upgrade?

It all works great now.

Thanks for your support.
tgus

____________________________
Families can be together forever...

 

[QatQat]

you are right,

until two weeks ago MyODBC 3.5 was still a development release; I see that it is now a production release.

Well, I am not going to upgrade mine anyway.

If it works, don't fix it!


Bye

Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[JustKIDn]

They went from 2.5x to 3.5x?
What happened to 3.0x?
tgus

____________________________
Families can be together forever...

 

[kingraoul]

I upgraded both MyODBC to latest build (.06) e mysql to 4.0.12 and i still get the #deleted behaviour. I hoped that both upgrades could help, but nothing. Now i'll try to use an old version of MyODBC, the one pointed by QatQat, but i still cannot believe to be haunted by this thing. Maybe the most users are running linux or using some other front end, but it still sound me strange no one has come to a final solution. (Or maybe I could not find it because of my fault!). Well, i can survive, for now, but i dont like to leave things undone, the real problem is the little time to devote to every thing. Have a nice day.

 

[JustKIDn]

Just found this;

http://www.mysql.com/products/myodbc/faq_4.html#DELETED_problem

Does this help?
tgus

____________________________
Families can be together forever...

 

[JustKIDn]

I also just found this one;

http://www.mysql.com/doc/en/MyODBC_clients.html

It's the same as the other one but more. Much more!

In fact you can probably ignore the last one and just go with this one.

Still don't know the significance to the 'timestamp(14)' field.

I'm a little concerned about it because I have a table that doesn't have a timestamp field.

I guess I'll have to add one so I can avoid the problems. (I hope)

tgus

____________________________
Families can be together forever...