On Tue, 25 Nov 2003 11:48:41 -0600 in comp.databases.ms-access,
LaurenW <re**********@nospam.zap> wrote:
Hi folks,
I just discovered that I have a table with a bunch of text fields that
ALL have the "Allow Zero Length" property set to "No", which is
apparently the default when you create a new text field. Please
correct me if I'm wrong about this. Somehow (I'm clueless about how),
some of my user's records have ended up with zero length strings in
some of these fields -- despite the restriction.
It's possible the restriction was lifted by a rougue programmer to
suit him/herself then put back again. I had a programmer who had
problems deleting test data due to RI violations so he stripped every
relationship out of the database. Actually, more than one, but at
least one I managed to find out before it went into production and
stood over the culprit while he put the relationships back in. The
other wasn't caught in time and caused major headaches.
The restriction is
not important to the application. But, now, when I execute a query to
move records from one table to another one with identical structure,
the query fails with the error: "1 or more records were not inserted
due to validation rule violations." There are NO, repeat N O fields
with validation rules set. However, when I manually change the "Allow
Zero Length" property for each of the text fields in the target table
to "Yes", the query works perfectly for all inserted records.
If this IS the cause of the problem, the obvious next question is: Is
there a way to programmatically change the "Allow Zero Length"
property of a text field in a linked back-end table FROM the front end
data base?
Please folks, this is a major crisis for my company. I'll be
eternally grateful for any advice on this problem.
It's not generally good practice to go modifying the schema on the fly
from the front end but here's a function you could run in your back
end:
Function AllowZLS()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb()
For Each tdf In db.TableDefs
If Not tdf.Name Like "MSys*" And Len(tdf.Connect) = 0 Then
For Each fld In tdf.Fields
With fld
If .Type = dbText Then
If Not .AllowZeroLength Then
.AllowZeroLength = True
End If
End If
End With
Next
Set fld = Nothing
End If
Next
Set tdf = Nothing
Set db = Nothing
End Function
If you absolutely must run from the front end then replace CurrentDb()
with a OpenDatabase() and add a db.close just before setting to
nothing.
Also, read Bruce's suggestion, having tables with nulls and Zero
length strings can cause mayhem when querying the table, the results
can be unpredictable (actually, I could predict them as I know how
nulls work in comparisons, or not as the case is) buit even with the
prediction it's a lot more work to query them. It's easy for the
computer to tell the difference between a null and a zero length
string but more difficult to the human eye.
--
A)bort, R)etry, I)nfluence with large hammer.
