Back to Top
The nuts and bolts for
Creative web development

Allow Zero Length and SQL create / alter table

Question:

With a 'create table' or an 'alter table' SQL statement I can add tables or columns to my online live Access database.
I can set new columns to non-required using 'null' in the SQL statement, but is there a way to set the 'Allow Zero Length' property to 'yes' as well?

Answer:

This can easily be done using ADOX instead of ADO/SQL.
(ADOX = Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security)

Here's an example that worked for me:

Sub flevAddHelpColumn()
    Dim oConn, oCat, oColumn
    ' open connection
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open MM_conn_STRING
    ' open ADOX Catalog
    Set oCat = Server.CreateObject("ADOX.Catalog")
    Set oCat.ActiveConnection = oConn
    ' create the new Column
    Set oColumn = Server.CreateObject("ADOX.Column")
    With oColumn
         Set .ParentCatalog = oCat ' Must set before setting properties
         .Name = "memHelp"
         .Type = adLongVarWChar
         .Properties("Nullable") = True
         .Properties("Jet OLEDB:Allow Zero Length") = True
    End With
    oCat.Tables("MetaExternalFields").Columns.Append oColumn
    ' finish up
    Set oColumn = Nothing
    Set oCat = Nothing
    oConn.Close()
    Set oConn = Nothing
End Sub

Some relevant links with more information on this subject are e.g.:

Marja Ribbers-de Vroed

Marja Ribbers-de VroedWell, what's there to tell?

I worked as a software engineer for a well-known Dutch software company for about 10 years. But then I decided I did not want to work there anymore.
Since then, I've been working with my husband in our own company.

I "discovered" web development early 2000, and I soon found out that I could actually create my own extra (DHTML) features in Dreamweaver by building custom extensions.
My first extension was Cross-browser AutoScroller, but I've developed several other ones since then.

See All Postings From Marja Ribbers-de Vroed >>

Share this Article