Allow Zero Length Strings - May 2000

Some time ago I wrote a function called 'SetAllFieldsAllowZeroLength' to allow all text and memo fields in a DAO [Access] database to accept zero length strings. I needed to do this because I had already distributed a database to lots of users. I could have changed the distributed database and required the users to begin using the new database but then they would have lost the data contained in their exiting database. Instead I added this function to the new version of the software and executed it only once. I recently revised the code, updated it to 'reverse' the action and made it the tip-of-the-month.

The Demo Program

The demo accepts a DAO database file name. Click the 'Update...' button to modify the database. An example:

Warning: This demo will change all fields in all non-system tables in the selected database from NOT allowing zero length strings to allowing them or vice versa. Don't run this demo against a production database unless you really want to perform this function.

Sample Database

I have included a sample database, with the downloadable code, which contains two tables. 

Each table contains one text field and one memo field. For example:

Browse for Database

Click the 'Browse' button to open a dialog which helps you locate a database.

Include in Your Code

To use this function include the following module in your code:

modUpdateTableAllowZeroLengthField.bas Allow Zero Length Strings

How It Works

The function works by iterating through the database's TableDefs collection. If it finds a non-system table, field with the allow zero length string attribute set to false then it changes it to true. The operative code:

For Each tbl In db.TableDefs
  With tbl
    If .Attributes = 0 Then 'only non system tables
        For Each fld In .Fields
            With fld
               If .Type = dbText Or .Type = dbMemo Then
                   If .AllowZeroLength <> bAllowZeroLength Then
                     .AllowZeroLength = bAllowZeroLength
                   End If
               End If
            End With
      End If
   End With

Demo References DAO 3.51

The demo project references DAO 3.51. You may need to change the reference to DAO 3.60 if you are trying to modify a Jet 4 [Access 2000] database. You may want to review "What I Learned About DAO 3.6 and Jet 4.0", the July 1999 tip-of-the-month.

The Demo Uses modConnectToWebSite

The demo program uses modConnectToWebSite.bas to display this page. For details on the use of that module click here.

Download VB Code

Click here to download the VB 6 Source Code [19k]. 

Associated Database Tips

Web Page - Click to View Month Year Volume
Allow Zero Length String May 2000 v3, n10
Backup and Restore a SQL Server Database September 2010 v14, n2
Connect to an MS Access Database using ADO April 2009 v12, n9
Copy Database with Data using DAO or ADO July 2003 v6, n5
Copy SQL Server Database Schema to Another March 2011 v14, n8
Create VB Class Module Code for ADO February 2003 v6, n7
Create VB Class Module Code for DAO September 2002 v6, n2
Create VB.Net Class to Process a SQL Table using ADO
      Add methods, Copy, Count, Compare
v12, n1
v14, n5
Create Jet Relationship using ADOX March 2003 v6, n8
Create SQL Relationship using SMO February 2009 v12, n7
Dynamically Update a SQL Database Adding Tables or Columns using SMO January 2011 v14, n6
Save Memo Fields in Database Using DAO March 2002 v5, n8
Store File in Jet Database October 2000 v4, n3
Update an Access Database with a Model's Schema using VB6 and DAO September 2000 v4, n2
Update an Access Database with a Model's Schema using VB6 and ADOX June 2003 v6, n11
Update an Access Database with a Model's Schema using VB.Net and ADOX August 2003 v7, n1
Use the OleDbDataAdapter to Read and Parse a CVS File into a DataTable or Collection October 2010 v14, n3
Using ADO to Read and Parse a Text File April 2002 v5, n9

Previous Tip   Tip Index   Next Tip


Updated 2009/03/29