When I Insert or Update a Record in Linked Tables, I Get #DELETED#


When using Microsoft Access as a front-end to MySQL through linked tables, you may see fields that contain #DELETED# instead of real data.


If the inserted or updated records are shown as #DELETED# in the access, then:

  • If you are using MS Access 2000, you should get and install the newest (version 2.6 or higher) Microsoft MDAC (Microsoft Data Access Components) from http://www.microsoft.com/data/. This will fix a bug in MS Access that when you export data to MySQL, the table and column names aren't specified. You should also download and apply the latest Microsoft Jet 4.0 Service Pack, which can be found at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114. This will fix some cases where columns are marked as #DELETED# in Access.
  • For all versions of Access, you should enable the MyODBC Return matching rows option.
  • You should have a timestamp in all tables that you want to be able to update. For maximum portability, don't use a length specification in the column declaration. That is, use TIMESTAMP, not TIMESTAMP(n), n < 14.
  • You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.
  • Use only DOUBLE float fields. Access fails when comparing with single floats. The symptom usually is that new or updated rows may show up as #DELETED# or that you can't find or update rows.
  • If you are using MyODBC to link to a table that has a BIGINT column, the results will be displayed as #DELETED. The work around solution is:
    • Have one more dummy column with TIMESTAMP as the data type.
    • Select the Change BIGINT columns to INT option in the connection dialog in ODBC DSN Administrator.
    • Delete the table link from Access and re-create it.
    Old records still will display as #DELETED#, but newly added/updated records will be displayed properly.
Source: http://www.mysqlab.net/knowledge/kb/detail/topic/odbc/id/5071

Tidak ada komentar: