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.:

Comments

Be the first to write a comment

You must me logged in to write a comment.