Oct 20 2007
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?
