TableDef/Set all text fields to allow zero length 
Author Message
 TableDef/Set all text fields to allow zero length

I have created a TableDef and I want to be set all text
fields properties to Allow Zero Length = True using VBA
code.  I would appreciate any assistance.

Thanks,
Jim



Mon, 24 Oct 2005 21:21:04 GMT  
 TableDef/Set all text fields to allow zero length


Quote:

> I have created a TableDef and I want to be set all text
> fields properties to Allow Zero Length = True using VBA
> code.  

Look up the Properties collection of the Field object...

Tim F



Tue, 25 Oct 2005 02:01:32 GMT  
 TableDef/Set all text fields to allow zero length
How to use Code to Set Allow Zero Length Strings in ALL Access Tables:
(Most of the credit for this goes to Cheryl Fischer. )

Make sure that you have a reference to DAO.
(Open a code module, Tools, References.)

Copy this code to a module and run it in the Immediate Window.
(Press Ctrl-G, then type the name of the procedure and press Enter.)

The code will check each table to see that if it is an ODBC attached table
or an ISAM attached table or an Access System table. It skips over those
types of tables.
Hopefully, all other tables are regular Access tables!

Then it checks each field to see if the type is Text or Memo (which includes
hyperlink.)
If it is, then it changes the AllowZeroLength to True.

If you need to exclude a table for some reason, you will need to modify the
code as it does ALL Access tables!

There is also error handling to catch the case where a table is open and the
property can't be modified. It ignores that field and keeps going.

Public Sub ChangeZeroLength()
On Error GoTo Err_ChangeZeroLength

Dim tdf As TableDef
Dim db As Database
Dim fld As Field

Set db = CurrentDb

For Each tdf In db.TableDefs
  'Debug.Print tdf.Name
  If (tdf.Attributes And dbAttachedODBC) Or (tdf.Attributes And
dbAttachedTable) Or (tdf.Attributes And dbSystemObject) Then
    'do nothing
  Else
    For Each fld In tdf.Fields
      'Debug.Print fld.Name, fld.Type
      'Note: dbMemo also includes hyperlink fields!
      If fld.Type = dbText Or fld.Type = dbMemo Then
        fld.AllowZeroLength = True
      End If
    Next fld
  End If
Next tdf

Set db = Nothing
MsgBox ("All text, Memo and Hyperlink fields now allow zero length
strings!")

Exit_ChangeZeroLength:
  Exit Sub

Err_ChangeZeroLength:
  Select Case Err.Number
    Case 3422   'table is in use
      Resume Next
    Case Else
      MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "SystemCode - ChangeZeroLength"
      Resume Exit_ChangeZeroLength
  End Select

End Sub

--
Joe Fallon
Access MVP


Quote:
> I have created a TableDef and I want to be set all text
> fields properties to Allow Zero Length = True using VBA
> code.  I would appreciate any assistance.

> Thanks,
> Jim



Wed, 26 Oct 2005 12:43:59 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Allow Zero Length setting

2. Allow Zero Length String in a field using SQL

3. make mdb field "Allow Zero Length"

4. Allow Zero Length Question

5. Zero length strings not allowed

6. Create Table "Allow Zero Length"???

7. Allow Zero Length

8. 'allow zero length' not working

9. Add Allow Zero Length Property?

10. Allow Zero Length in Access DB

11. DAO 3.0 and Allow Zero Length

12. Jet OleDB: Allow zero Length property returns True

 

 
Powered by phpBB® Forum Software