[AccessD] Which properties - used to be - change all fields to Allow Zero Length via VBA

Bob Heygood bheygood at abestsystems.com
Tue Jul 11 09:06:19 CDT 2006


In looking in the archives and elsewhere I see that some properties seem to
either not be able to be changed via code or are very difficult to do so. In
particular some code creates a new field and then deletes the old.

Does someone have a list of those that are modifiable and not?/?

bob



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of MartyConnelly
Sent: Sunday, July 09, 2006 11:44 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] change all fields to Allow Zero Length via VBA


The AllowZeroLengthString property is exposed through the Field object
children of the TableDef object. The solution is simply a matter of
programmatically changing this property for all fields in a selected
table, or indeed in all tables in a database.

Here's the code: courtesy Lamont Adams

Public Sub ChangeZeroLengthSetting(strTableName As String)
On Error Resume Next
Dim db As Database
Dim tdf As TableDef
Dim fld As Field

'Try to get a pointer to the selected table
Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTableName)

'Oops
If Err.Number Then
MsgBox Err.Description
Set tdf = Nothing
Set db = Nothing
Exit Sub
End If

'Now we have the tabledef open, iterate through the
'fields collection, and change the appropriate property
For Each fld In tdf.Fields
If fld.Type = DB_TEXT Or fld.Type = DB_MEMO Then
'AllowZeroLength property is only available for
'text or memo fields
fld.AllowZeroLength = True
End If
Next fld

'Clean up
Set tdf = Nothing
Set db = Nothing

End Sub

This method can be applied to change other properties, assuming the
desired property is write-able from DAO—many aren't. All tables in a
database can be changed with the addition of an outer For each tdf in
db.Tabledefs loop; just be sure to examine the Attributes property for
the dbSystemObject bit. Access will not let you change the field
properties for one of the System tables.

Bob Heygood wrote:

>Good Morning to the list,
>
>I find myself with 2 new tables I imported from Excel with over 321 fields.
>All fields are text 255.
>I don't think I can face manually changing all of them to "Allow Zero
Length
>String".
>
>In the archives I could not find a way to do so easily.
>Some thing like iterate thru all the fields in the table via DAO, changing
>the property....
>
>Any code out there?
>
>A2K
>
>TIA
>
>Bob Heygood
>
>
>
>
>

--
Marty Connelly
Victoria, B.C.
Canada

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list