MySQL – How to Link a Table with Binary Key into MS Access

ms accessMySQLodbc

In MS Access I've created a linked table via ODBC to a MySQL table with a binary(16) primary key column (UUID). MS Access only shows the first row, all other rows have #deleted entries. I guess the reason for this is the binary field. I've checked the ODBC driver properties without success. Do you have a solution to this problem?

Details: I'm using Access 2010 64 bit and "MySQL ODBC 5.2a Driver". Opening the linked table looks like this:

linked_table

The MySQL table has following Definition (reduced):

+---------------+---------------+------+-----+------------------+-------+
| Field         | Type          | Null | Key | Default          | Extra |
+---------------+---------------+------+-----+------------------+-------+
| LNEr          | binary(16)    | NO   | PRI |                  |       |
| Datum         | datetime      | YES  |     | NULL             |       |
| StatNr        | int(11)       | YES  |     | NULL             |       |

MS Access generates following Definition:

Access_definition

Best Answer

I solved this by creating a updateable VIEW to the table. The view displays every column but the primary key LNEr is converted to a string with the HEX() function:

CREATE VIEW v_ereignisse AS SELECT HEX(LNEr) AS LNEr, Datum, StatNr, ...

In MS Access instead of creating a link to the table I created a link to the view. Because the view has no primary key, the "table link manager" asks for selecting one, this (of course) is the string version of LNEr.

Related Question