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

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.