ACCESS: SQL create table statement that will add a comment (Description) to each column

Question

I want to add a description to each column as I create the table:

Example:

strSQL = "CREATE TABLE Table1 (Field1 VarChar(250) Not Null UNIQUE, Field2 VarChar(250) Not Null COMMENT 'Test It')"

I saw somewhere where there was a data type of COMMENT. Is there a way to do this within the SQL statement?

I tried different variations using COMMENT and DESCRIPTION, but no luck. Is this even possible while executing a create table SQL statement?

Answer 1

Create Description property, if not exists (@HansUp)

Set prpNew = CurrentDb.TableDefs(tableName).Fields(i).CreateProperty()
            prpNew.Name = "Description"
            prpNew.Type = dbText
            prpNew.Value = "New description"
CurrentDb.TableDefs(tableName).Fields(i).Properties.Append prpNew

Or Update Description property

CurrentDb.TableDefs("Table1").Fields("Field1").Properties("Description").value="This is description"

See example

Public Function TestFieldComment() As String
Dim i As Long
Dim sDescr As String
Dim prpNew As Variant
Const tableName = "tbTest"
    For i = 0 To CurrentDb.TableDefs(tableName).Fields.Count - 1
        On Error Resume Next
        sDescr = CurrentDb.TableDefs(tableName).Fields(i).Properties("Description").Value
        If Err.Number = 3270 Then
            Err.Clear
            Set prpNew = CurrentDb.TableDefs(tableName).Fields(i).CreateProperty()
            prpNew.Name = "Description"
            prpNew.Type = dbText
            prpNew.Value = "Description"
            CurrentDb.TableDefs(tableName).Fields(i).Properties.Append prpNew
        End If
        
        CurrentDb.TableDefs(tableName).Fields(i).Properties("Description").Value _
                = "This is description for " _
                  & CurrentDb.TableDefs(tableName).Fields(i).Name
    Next i
    TestFieldComment = "Ok"
End Function

Answer 2

I would like to thank everyone for the help. This worked great. Did not realize I needed Microsoft DAO library. Have not used DAO in a while. Here is the complete code:

Function fnSetDescriptions(strDBFile As String, strTable As String) As Boolean
'Ensure Microsoft DAO 3.6 Object Library is referanced
Dim dbDatabase As DAO.Database
Dim tbTable As DAO.TableDef
Dim flFields As DAO.Fields 'Could use this in the loop with a With
Dim aryProperties As Variant
Dim strFldName As String
Dim intCnt As Integer
Dim strDescrip As String
    fnSetDescriptions = False
    Set dbDatabase = OpenDatabase(strDBFile)
    Set tbTable = dbDatabase.TableDefs(strTable)
    With tbTable
        For intCnt = 0 To .Fields.Count - 1
            On Error Resume Next
            'This is a test to see if the property "Description" is there, which it's NOT since the table was just made.
            '   Will generate an error.
            strDescrip = .Fields(intCnt).Properties("Description").Value
            strFldName = .Fields(intCnt).Name
            strDescrip = "This is the description for " & strFldName
            'If the property does not exist, an error will occur
            '   3270 is the number, "Property does not exist" is the description
            If Err.Number = 3270 Then
                'Description property does not exist
                Err.Clear
                Set aryProperties = .Fields(intCnt).CreateProperty()
                aryProperties.Name = "Description"
                aryProperties.Type = dbText
                aryProperties.Value = strDescrip
                .Fields(intCnt).Properties.Append aryProperties
                Erase aryProperties
            ElseIf Err.Number = 0 Then
                'Means Description property already exists
                 .Fields(intCnt).Properties("Description").Value = strDescrip
            Else
                GoTo GetOut
            End If
        Next
    End With
    fnSetDescriptions = True
GetOut:
    dbDatabase.Close
    Set tbTable = Nothing
    Set flFields = Nothing
    Set dbDatabase = Nothing
End Function