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?

Iklan

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout /  Ubah )

Foto Google

You are commenting using your Google account. Logout /  Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout /  Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout /  Ubah )

Connecting to %s