Getting #deleted# when updating data to a MySql table

[EdLentz]

I am trying to use a MS Access 2000 front end for a MySql DB. I have MDAC ver 2.8, Jet4 SP8, on a WinXP Home mach. trying to update tables to a MySql 4.0.15 server. I have MySql ODBC driver version 3.51.06 (latest is 3.51.11, but from what I have read the .11 is buggy). I have a form that I am wanting to use a combo box to enter customer info from another table. I have tried deleting the link to the tables, the ODBC "Return Matching rows" is checked. I have a timestamp field in the DB and on the form. Now when I use the combobox all my fields are #deleted#, if I close the form and open it again the info is there. At the end of my combo code I refresh the page. Here's the combo box code:

Private Sub Combo66_AfterUpdate()
Dim customer As DAO.Recordset, SQLTEXT
SQLTEXT = "select * From cust where " & "[cust] = '" & Me![client] & "';"
Set customer = CurrentDb.OpenRecordset(SQLTEXT)
Me![client] = customer![cust]
Me![add1] = customer![add1]
Me![city] = customer![city]
Me.Refresh
Me.Refresh
End Sub

Does someone with more knowledge than me see what I am doing wrong??
Thanks for any and all replies

Ed

 

[PHV]

Does have the MySql table a PrimaryKey recognised by access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EdLentz]

Yes if I go into the linked table in Access it has a primary key in each of the tables I am having issues with.

 

[jsteph]

Ed,
Sometimes if you change a field but do NOT refresh the link in Access, you'll get this. Try refreshing the link.
--Jim

 

[EdLentz]

jsteph,
Thanks for the suggestion. I've refreshed and even deleted the links and reconnected them with the same results. It is maddening

ed

 

[jsteph]

Ed,
I was looking for the db I recently did where I had a similar thing, and couldn't find it. But the issue I had had something to do with the data itself--not corrupt but the translation through ODBC wasn't pure. I might've been a char field over 255 chars that didn't translate to memo, something along those lines.

Also, when you link, if odbc asks you to define the key (with that popup list of available fields), this could be it--it will do this if the backend has no unique key, and if the data violates the key chosen (even though it's valid on the server), odbc will show the #deleted#.

I'll look around some more and see if I can find the db in which I had that problem.
--Jim

 

[EdLentz]

Jim,
Well with the combo box there shouldn't be a problem with a text or char field with that many chars. I'll check. I might have a field in the backend that wants data and not writing data to it with the combo box. Funny tho I have another Access DB that is doing some other stuff with the same backend and it works fine, thru the same ODBC connection.

Thanks

Ed

 

[danvlas]

This happens if you have BIGINT fields in MySql. Change them to INT and I think you'll be home free.

On the MySql site they say that you also should include a Timestamp field in all your tables, but I didn't need to. Just removing the darn BIGINT fixed the problem.

HTH







Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[EdLentz]

I think I finally figured it out. Daniel had the right track. I had some fields in Mysql set as tinytext and Access didn't like that once I changed those fields to varchar all worked. Many thanks to all of you that lended your expertise!

 

[jbz]

I am getting a similar problem, but I am liking to a read-only Oracle database. Every time I open the table, I get #Deleted in every field. I can filter for certain data, then turn filter back off, and the data is magically all there! BUT, when I run a query, all I get is #Deleted no matter what. The Oracle table has 5 fields making up the key, and they all show as such in Access. I do not think I have the option of changeing the fields from Bigint to Int... Any suggestions?