Programmatically changing table property

[John]

Hi

How can I Programmatically change the Allow Zero Length table property to
Yes?

Thanks

Regards

 

[aaron.kempf]

you don't need 'allow zero length' it is a lemon

-Aaron

 

[John]

I have a relatively large database with several tables. How can I change
this property to yes for all text fields in all tables? I am using below
code but it says 'Property not found'.

Thanks

Regards

Sub TurnOnAllowZeroLength()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer

Set MyDB = CurrentDb
propName = "AllowZeroLength"
propType = 10
propVal = "No"
rplpropValue = "Yes"
intCount = 0

For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
' **** Error comes on the below line ****
If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intCount = intCount + 1
End If
End If
Next i

MyDB.Close

If intCount > 0 Then
MsgBox "The " & propName & " value for " & intCount & " non-system
tables has been updated to " & propVal & "."
Else
MsgBox "No change needed"
End If

End Sub


you don't need 'allow zero length' it is a lemon

-Aaron

 

[Arvin Meyer [MVP]]

You need some error handling to deal with adding the property:

If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
Else
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Number & " on Table " _
& MyDB.TableDefs(i).Name & "."
MyDB.Close
Exit Function
End If

 

[Steve Schapel]

John,

It looks like your code is trying to manipulate the AllowZeroLength
property as if it were a property of the table. This property does not
apply to the table itself, only to fields.

I believe this is how I would do it:

Sub TurnOnAllowZeroLength()
Dim MyDB As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim intCount As Integer
Set MyDB = DBEngine(0)(0)

For Each tdf In dbs.TableDefs
If tdf.Name Like "MSys*" Then
' ignore
Else
For Each fld In tdf.Fields
If fld.Type = "Text" Then
fld.AllowZeroLength = True
intCount = intCount + 1
End If
Next fld
End If
Next tdf

If intCount > 0 Then
MsgBox "The Allow Zero Length property for " & intCount & "
tables has been set to 'Yes'."
Else
MsgBox "No change needed"
End If

Set dbs = Nothing

End Sub

Having said that, I must agree with Aaron that this is an odd thing to
be doing. The Allow Zer Length property is set to Yes by default in
Access, and most developers routinely change it to No.

 

[Steve Schapel]

John,

In reviewing my earlier answer, I think in my haste there is an error
(at least). Sorry, should be:
If fld.Type = dbText Then

 

[Tony Toews [MVP]]

How can I Programmatically change the Allow Zero Length table property to
Yes?

Why would you want to do this? Just curious.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/