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. :confused:

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. :confused:

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