odbc linked table shows #deleted

[Guest]

I import a database (include 8 tables) from mysql. With the import it works
fine. But when I am traying to link the tables, from the 8 tables only one
shows up with #deleted. The others work fine.
And it was working before.

What could happened?

 

[Joe Fallon]

Access probably can't identify the Primary Key on that table.
Even if one exists, Access tends to take the first index in alphabetical
order, so that if you have 3 indexes and the PK is the 2nd one Access does
not use it, it uses the first one.

If you were using SQL Server I would tell you to also add a Timestamp field
to each table.
Never used MySQL so I do not know if the equivalent data type exists.

Finally, you can create a "Fake" index in Access that tells Access which
columns make
a Unique record in the linked table. The index is only used by Access is
completely unknown to the Server.

The following example creates an index on an ODBC linked table. The table's
remote database is unaware of and unaffected by the new index. The following
example won't work if you simply paste it into Microsoft Access; you first
need to create an ODBC linked table called OrderDetailsODBC.

(CREATE INDEX Statement in Help has some examples)

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);

 

[Gord]

Check the MyODBC documentation at

http://dev.mysql.com/doc/mysql/en/msaccess.html

specifically,

http://dev.mysql.com/doc/mysql/en/deleted-problem.html

Make sure that you have the required updated to MDAC and Jet.

You also need a TIMESTAMP column in your MySQL tables, and the ODBC
connector needs to have the "Return matching rows" option enabled.

And yes, your tables should have a primary key defined.