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
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
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
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.
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/
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.