Create a field using SQL statement

--Get the name of the primary key constraint
sp_helpconstraint test


--drop the current primary key
ALTER TABLE TableA DROP CONSTRAINT NameOfPrimaryKeyConstraint
GO

--add the new column
ALTER TABLE TableA ADD Label char(50) CONSTRAINT Label_default DEFAULT '12M'
GO

--recreate a primary key that includes the new column
ALTER TABLE TableA ADD CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
   (OriginalColumnForPrimaryKey, Label) ON PRIMARY
GO

Where 'test' is the name of your table:

sp_helpconstraint test

Okay, I want to do this using ADO.  Here is what I have but I get an error.  It doesn't seem happy with NameOfPrimaryKeyConstraint

Dim rstTableList, rstTemp As ADODB.Recordset

Dim varTableList() As Variant
Dim RrdCount As Integer
Dim intCount, i As Integer
Dim strRule As String
Dim strTable As String


Set rstTableList = New ADODB.Recordset
rstTableList.Open "tblDataEntryTableNames", CurrentProject.Connection

    rstTableList.MoveFirst
    varTableList() = rstTableList.GetRows
    RrdCount = UBound(varTableList, 2)
   
If varTableList(0, i) = "ftbl12M_CESD" Then
    For i = 0 To RrdCount - 1
        'strSQL = "Alter Table " & varTableList(0, i) & " alter column ID Int;"
           
   
        '    --drop the current primary key
        strSQL1 = "ALTER TABLE " & varTableList(0, i) & " DROP CONSTRAINT NameOfPrimaryKeyConstraint"
        'GO
       
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL1)
        DoCmd.SetWarnings True
       
        '--add the new column
        strSQL2 = "ALTER TABLE " & varTableList(0, i) & " ADD Label char(50) CONSTRAINT Label_default DEFAULT '12M'"
        'GO
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL2)
        DoCmd.SetWarnings True
       
        '--recreate a primary key that includes the new column
        strSQL3 = "ALTER TABLE " & varTableList(0, i) & " ADD CONSTRAINT ID PRIMARY KEY CLUSTERED (OriginalColumnForPrimaryKey, Label) ON PRIMARY"
        'GO
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL3)
        DoCmd.SetWarnings True
   
       
    Next
Else
End If

End Sub

Sorry, I expected you to run these queries manually and replace NameOfPrimaryKeyConstraint and OriginalColumnForPrimaryKey with values you got from running sp_helpcontraint.

Do you just need to do this one time, or do you need to be able to do it every time someone runs a function of your application? Also, are you running SQL Server or just MSDE? I ask because it'll be easier to write T-SQL to do it once and describe how to execute it in Query Analyzer than to do it in VBA.