1

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

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.