Adding a field

  • Thread starter Thread starter Darren
  • Start date Start date
D

Darren

When adding a field to a table from within the Access DB Designer, a text
field will default to "Unicode Compress" = True

When adding it through SQL it defaults to false.

Is there anyway to get them to default to true using SQL?

Thanks
 
Yes, it is rather annoying that JET 4 sets several properties
inconsistently, depending how you create the field.

AFAIK, there is no way to set the default behavior for DDL queries. But if
you execute them under ADO, you can use the WITH COMP option, e.g.:
CREATE TABLE Table1 (Field1 TEXT (50) WITH COMP);

I don't believe that works under DAO, so it probably won't work if you try
it in the query window. Has to be executed as:
CurrentProject.Connection.Execute "CREATE ...
 
Very nice, couldn't find that anywhere.

Anything for "Allow Zero Length" to be true?

Thanks
 
I don't think that AllowZeroLength can be set with DDL.

You can use DAO to set:
Currentdb.TableDefs("Table1").Fields("Field1").Properties("AllowZeroLength")

It is also possible through ADOX (though older versions can fail on this.)

BTW, I can hardly imagine why you would want this property set to True.
Every query or VBA routine that refers to the field must then test for a
zero-length string as well as a null, which seems error-prone, harder to
maintain, slower to develop, and slower to execute, for no benefit.
 
I am using ADO from a Delphi App.
The problem here is if a user enters a value into one of these fields and
then later clears it.
ADO sends an empty string as the field value, not NULL.
Resulting in a DB Error.
 
Back
Top