Allow Zero Length String in MS Access 2002 and 2003

[AK]

Folks,

I am not sure how many of you encountered this issue. The rocket scientists
at Microsoft decided to change the default for Text columns' allow zero
length string attribute to Yes, from No. This broke many of my existing
applications because everywhere I was expecting, "Column is Null" construct
to work, stopped working (because zero length strings were sitting in those
columns). I have no idea why the Engineers at Microsoft decided to change
the default, it does not make sense rationally. For example, Oracle
automatically converts zero length string to a Null value when stored in a
varchar2 column. Either way, whatever the default should be, it is
generally a bad idea to just change the functionality because some yoyo felt
like it.

Considering that I am a nice guy, I am contributing this little script to
save you the hell to convert this default to No and fix the existing data.
Cynics, please keep your opinion to yourself.

Option Compare Database
Option Explicit

Sub FixZeroLengthData()
Dim DB As DAO.Database
Dim tbl As DAO.TableDef
Dim nCnt1&, nCnt2&
Set DB = DBEngine.Workspaces(0).Databases(0)
For nCnt1 = 0 To DB.TableDefs.Count - 1
If CBool(DB.TableDefs(nCnt1).Attributes And dbSystemObject) Then
GoTo Next_Rec
End If
For nCnt2 = 1 To DB.TableDefs(nCnt1).Fields.Count - 1
If DB.TableDefs(nCnt1).Fields(nCnt2).Type <> dbText Then
GoTo Next_Field
End If
' Change the zero length string to Null
DB.Execute "update " & DB.TableDefs(nCnt1).Name & " set " &
DB.TableDefs(nCnt1).Fields(nCnt2).Name & "=Null where '' & " &
DB.TableDefs(nCnt1).Fields(nCnt2).Name & "=''"
If DB.TableDefs(nCnt1).Fields(nCnt2).AllowZeroLength = True Then
Debug.Print "Modifying " & DB.TableDefs(nCnt1).Name
DB.TableDefs(nCnt1).Fields(nCnt2).AllowZeroLength = False
End If
Next_Field:
Next
Next_Rec:
Next
Debug.Print "Done!"
End Sub

 

[Allen Browne]

AK, thanks for posting this. Like you, I was incredulous that basic
consistency and functionality in Access was broken by these nonsense
settings when Access 2000 was realeased. Four years later, it is still an
issue for everyone.

While the AllowZeroLength is probably the worst example for data integrity,
there are other problem properties as well, e.g.:
- The Name AutoCorrect properties of the datatbase are arguably as bad, as
they corrupt the database.

- The SubdatasheetName of TableDefs causes performance issues and exposes
data that was never intended.

- Displaying the Properties box for forms by default so users can change
their forms while in use is just a nonsense (not to mention the
corruptions).

More details in article:
Problem properties
at:
http://members.iinet.net.au/~allenbrowne/bug-09.html