MS Access – MySQL Zero-Length String Problem

I use MS Access to connect to my MySQL database, using MyODBC.

Here is a “simple” problem that give me quite a headache.

• I create a table on MySQL database with VARCHAR field, NOT NULL
• I link my table on my MySQL to MS Access.
• I create a form based on that table.
• Somehow, MS Access always treat Zero-Length String as NULL. I cannot find any option to make MS Access Zero-Length String value as “Zero-Length String”. So, if I try to empty a control that links to a VARCHAR field, an error pop up with message: “You tried to assign Null value to a variable that is not a Variant data type”.

For a while, I come up with this solution:
Set form KeyPreview property to Yes
Then insert some code in Form_KeyUp:

Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
If Len(ActiveControl.Text) = 0 Then ActiveControl = ""
End Sub

It works for me. I wonder if someone out there can come up with better solution.
Anyone?