Add New Field

[accesser]

How can I programmatically add new field on an existent table.

 

[Mile-O]

Using ADO or DAO?

 

[accesser]

Please give me both ways DAO and ADO

Thanks

 

[Mile-O]

Here's a function in ADO. I believe the help files already have an example for DAO.
Read the notes in the comments about the reference libraries needed.

Public Function AddField(ByVal TableName As String, FieldName As String, _
    FieldType As DataTypeEnum) As Boolean
'------------------------------------------------------------------------------------------
' Procedure: AddField
'    Author: Stewart J. McAbney
'   Created: Jun 21 2005
'  Input(s): TableName (String)       - Name of table to append field to
'            FieldName (String)       - Name of field to be appended
'            FieldType (DataTypeEnum) - Data type of field
' Output(s): AddField (Boolean)       - Success status of function
'   Purpose: To add a field to a table
'     NOTES: The following reference libraries are required:
'             - Microsoft ActiveX Data Objects 2.x Library
'             - Microsoft ADO Ext. 2.x for DDL and Security
'------------------------------------------------------------------------------------------

    On Error GoTo Err_AddField

    ' Variable declaration
    Dim cn As ADODB.Connection  ' ADODB.Connection object
    Dim cat As ADOX.Catalog     ' ADOX.Catalog object
    Dim tbl As ADOX.Table       ' ADOX.Table object
    
    ' Set object variabe connections
    Set cn = CurrentProject.Connection
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cn
    
    ' Set ADOX.Table object to specified table
    Set tbl = cat.Tables(TableName)
    
    ' Append specified field name to ADOX.Table object
    tbl.Columns.Append FieldName, FieldType

    AddField = True ' Return True, field has been appended
    
Exit_AddField:
    cn.Close            ' Close ADODB.Connection object
    Set tbl = Nothing   ' Release ADOX.Table object from memory
    Set cat = Nothing   ' Release ADOX.Catalog object from memory
    Set cn = Nothing    ' Release ADODB.Connection object from memory
    Exit Function
    
Err_AddField:
    AddField = False    ' Return False, field has not been appended
    Resume Exit_AddField

End Function ' AddField

 

[Jibbadiah]

Here is one way to create a new column based on todays date.

Today = (Format(Now(), "dd/mm/yyyy"))
newcolumn = Left(Today, 2) & Format(Today, "mmm") & Right(Today, 2)

strSQL = "ALTER TABLE ALL_CAMPAIGN_CODES_TB ADD COLUMN " & newcolumn & " INTEGER;"

DoCmd.RunSQL (strSQL)

Note: this should be used sparingly - otherwise you will end up with a massive table. Consider setting up another table if you were thinking about adding a new column regularly.

James