Modify data structure using code

[Phillb]

I want to be able to make code that will modify a data structure in another file. Can I do it in an access application or will I need to make a VB application? I want to add some more fields to a table.

 

[doco]

I want to be able to make code that will modify a data structure in another file. Can I do it in an access application or will I need to make a VB application? I want to add some more fields to a table.

Three approaches

Sub field_DAO()
Dim db As DAO.Database
Dim tbl As DAO.TableDef

    Set db = CurrentDb
    Set tbl = db.TableDefs( YourTable )

    With tbl
        '    add new field
        .Fields.Append .CreateField( NewFieldName, NewFieldType, Precision )
    End With
    Set db = Nothing
    Set tbl = Nothing
End Sub

Sub field_ADOX()
'    must set proper references to ADO Ext 2.x
Dim cat As New ADOX.Catalog

    cat.ActiveConnection = ActiveProject.Connection
    With cat.Tables( YourTable )
        '    add field
        .Columns.Append "NewFieldName", NewFieldType, Precision
    End With
    Set cat = Nothing
End Sub

Sub field_SQL()
Dim conn As ADODB.Connection
Dim sql As String

    Set conn = CurrentProject.Connection
    sql = "Alter Table YourTable " & _
           "Add COLUMN NewFieldName NewFieldType( Precision )
    conn.Execute sql
    Set conn = Nothing
End Sub

Of course appropriate error trapping and peripheral coding assumed