I have an access databse (from an opensource project) that's in Access 2000 format.
All text fields, I've looked at, have ALLOWZEROLENGTH set to TRUE and DEFAULT to "" (empty string).
On conversion to Access 97 (using Access 2003) the ALLOWZEROLENGTH is set to FALSE but the DEFAULT stays at empty string.
Bizarrely (it seems to me) I can still add a record to a table without specifying values for many of the text fields without a problem.
However if I modify the table design by adding a field, thereafter if I try to add a record lots of 'cannot contain zero length string' errors are triggered.
It's like the DEFAULT value properties on the text fields are dormant until then.
Is this a problem with the convertor in Access 2003?
I also tried converting first to 2003 and then back to 97 but the result was the same
Is the best action for me to do to go through the 97 database and set ALLOWZEROLENGTH to TRUE or set the DEFAULT to Null?
The "best action" is to not use the Access 97 database format. Office 97 is no longer actively supported by Microsoft; that has been the case for over nine (9) years now! (ref: here)
However, if you absolutely must use the Access 97 file format for some reason then yes, you probably should just go through the table definitions and adjust the field properties as required.