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.
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
If .Attributes = 0 Then 'only non system tables
For Each fld In .Fields
If .Type = dbText Or .Type = dbMemo Then
If .AllowZeroLength <> bAllowZeroLength Then
.AllowZeroLength = bAllowZeroLength
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
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