Use code to add fields to Access table

Question

Hi

Anyone have some code on how to add fields to table through?

Thanks

Answer 1

Have a look at this thread...

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/7c5a749d-a479-4486-abf8-e1c9d49ef057

and/or...

http://www.access.qbuilt.com/html/vba.html

--

Gina Whipp

2010 Microsoft MVP (Access)

"Steve Staab" wrote in message news:fb78ace3-cd55-4df2-8516-9de752605812...

I tried the code you suggested but get an "Invalid argument" debug error on

'tbl.Columns.Append col'. I also tried 'tbl.Columns.Append "MyDecimal" '

but that failed with the same error.

Help

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 2

I tried the code you suggested but get an "Invalid argument"  debug error on   'tbl.Columns.Append col'.  I also tried 'tbl.Columns.Append "MyDecimal"  ' but that failed with the same error. 

I also changed the table name from tblAdoxContractor to one of my tables

 Help

Answer 3

do you have any requirements for a particlar application or language? Here's an example of doing it using ADOX.

Function ModifyTableAdox()

    'Purpose:   Show how to add fields to a table, and delete them using ADOX.

    Dim cat As New ADOX.Catalog

    Dim tbl As ADOX.Table

    Dim col As New ADOX.Column

    'Initialize

    cat.ActiveConnection = CurrentProject.Connection

    Set tbl = cat.Tables("tblAdoxContractor")

    'Add a new column

    With col

        .Name = "MyDecimal"

        .Type = adNumeric   'Decimal type.

        .Precision = 28    '28 digits.

        .NumericScale = 8   '8 decimal places.

    End With

    tbl.Columns.Append col

    Set col = Nothing

    Debug.Print "Column added."

    'Delete a column.

    tbl.Columns.Delete "MyDecimal"

    Debug.Print "Column deleted."

    'Clean up

    Set col = Nothing

    Set tbl = Nothing

    Set cat = Nothing

End Function

Credit:

http://allenbrowne.com/func-adox.html#ModifyTableAdox

---

Blah

Answer 4

Try something like

CurrentDb.Execute "ALTER TABLE T1 ADD COLUMN C1 TEXT(25)"

---

Peter (http://access.xps350.com/)