How to insert a column description into an Access table?

Question

How can I insert a description for a column in an Access table using SQL?

I do:

CREATE TABLE TAB_A (COLUMN1 TEXT(30), COLUMN2 REAL, PRIMARY KEY (COLUMN1)

but how can I insert a description for each column?

Answer 1

Dim col As ADOX.Column = New ADOX.Column
With col
  .Name = name
  .Type = type
  .DefinedSize = size
  .ParentCatalog = cat
  .Properties("Description").Value = description
End With

Answer 2

You can't do it in SQL.

KB210314: ACC2000: How to Use ADO or DAO to Retrieve a Field's Description

I reckon that it can be set the same way that it can be retrieved:

Function SetFieldDesc_ADO(ByVal MyTableName As String, ByVal MyFieldName As String, ByVal Description As String)

   Dim MyDB As New ADOX.Catalog
   Dim MyTable As ADOX.Table
   Dim MyField As ADOX.Column

   On Error GoTo Err_SetFieldDescription

   MyDB.ActiveConnection = CurrentProject.Connection
   Set MyTable = MyDB.Tables(MyTableName)
   MyTable.Columns(MyFieldName).Properties("Description").Value = Description

   Set MyDB = Nothing

Bye_SetFieldDescription:
   Exit Function

Err_SetFieldDescription:
   MsgBox Err.Description, vbExclamation
   Resume Bye_SetFieldDescription
End Function

Answer 3

My question to you: considering you cannot 'query' a table for a column's description using SQL, how do plan to read it? Odds are, the answer to my question will be the same as the answer to your question :)

Answer 4

FWIW I can't get the DAO (v3.6) method to work.

(C#) Both .Properties["Caption"] and .Properties["Description"] throw a COMException "Property not found." Examining the Properties collection does, in fact, corroborate this.

(...would have appended this to Tomalak's answer, if only I was authorized to do so.)

Could MS have such blatantly wrong documentation up? Anyone know how to make it work in DAO for real?