"#Delete" <-- This isn't my data!!! Help!

[richy240]

I am new to MySQL, by the way.

What is happening here? First, my setup:

- MySQL 4.0.18 running on Windows XP (server)
- Access 2002 using ODBC DSN 3.51.71 (client)

My tables are InnoDB due to the record-level locking. I am migrating my data from Access to MySQL, and I will continue using the application already designed in Access with MySQL as the back-end. I am taking a single-user database app to a multi-user app.

I have already followed the recommendation of MySQL AB and installed all updated datastore-related software including MDAC 2.8 and Jet 4.0 SP8.

Now, the problem:

There are three tables (of 11) in my database where (all) the data shows as "#Deleted" when on the client-side of my ODBC connection, in this case Access (either in forms or in the table's datasheet view).

PLEASE HELP!

I have created completely new databases and linked my tables, but this does not make a difference. Also, I tried to make the configuration of the tables as simple as possible. Nothing thus far has helped.

Thanks in advance.

 

[richy240]

I should also mention that lookups on these tables based on a value from another table, such as in my subforms and the like, still seem to work without a problem. Well, it fails to display the actual data, but functionally it seems to work all right.

 

[IronBeaver]

You could start here:

http://dev.mysql.com/doc/connector/odbc/en/faq_4.html#DELETED_problem

Also, if your backend is running over a network, make sure you avoid such VB intrinsic constants as acAnywhere in things like docmd.FindRecord field, acAnywhere, .... Otherwise, you will end up dumping your ENTIRE table across the network, and if its big, it runs SLOW!

 

[richy240]

I was able to get the problem fixed, but that page was not helpful in the least. (I appriciate the response though!) The problem was that during the conversion from Access 2002 to MySQL, the 'Number' datatype was converted to 'BigInt' instead of 'Int'.

Although the option ("Change BIGINT to INT") was checked in the ODBC Options form, the data was being displayed as "#Deleted"*. Once I changed the datatype from BigInt to Int on the MySQL-formatted tables, the data showed correctly.

Thanks for the help!


*Notice the data was not "#Deleted#", but "#Deleted". I was able to find very little documentation regarding this error. Luckily I was able to come across a little bit of information on which I was able to expand.