How do you set "Allow Zero Length" to Yes from SQL?

[JA3395]

I'm trying to use a Query to create a table so that when the fields change, as they are doing regularly right now, I can just modify the script and re-execute.

I have a problem though.

CREATE TABLE [Table_Name]
(Field1 TEXT(18) NULL,
(Field2 TEXT(255) NULL);

Does not set the "Allow Zero Length" to Yes.

ALTER Table isn't any different.

Can anyone tell me how to do this in SQL?

Regards,

J.

 

[AlexCuse]

I have run into troubles like this as well. What I have found its' easiest to do when I need to create tables 'on the fly' for users is have an empty table that I use as a template, and execute a make-table query using that. I have always found access's DDL just a bit awkward.

This may or may not be an option for you. I will be watching this thread to see what the experts have to say

Ignorance of certain subjects is a great part of wisdom

 

[genomon]

I don't know how it would be possible to set table properties using any version of a Structured Query Language.
I call the following VBA after the tables are created to allow zero-length text data:

Private Sub AllowZeroLength()
Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim bolAllowZeroLength As Boolean

Set db = CurrentDb

bolAllowZeroLength = True

For Each tbl In db.TableDefs
  With tbl
  'Debug.Print tbl.Name
    If .Attributes = 0 Then 'only non system tables
        For Each fld In .Fields
        'Debug.Print fld.Name
            With fld
               If .Type = dbText Or .Type = dbMemo Then
                   If .AllowZeroLength <> bolAllowZeroLength Then
                     .AllowZeroLength = bolAllowZeroLength
                   End If
               End If
            End With
        Next fld
      End If
   End With
Next tbl
Set db = Nothing
End Sub

Hope this helps!

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.