Paradox
Paradox

Reputation: 4556

How to create a field in in a preexisting table with VBA for MS Access?

I have a Database Database and a table Table. I have found online how to create a field from a newly created database and table in VBA in MS Access, but I do not know how to do this when the database and table already exist before the macro has been run.

To do it without a preexisting database and table, I can run the following:

Sub CreateTable()

    Dim db As DAO.Database
    Dim table1 As DAO.TableDef

    Set db = CurrentDb

    Set table1 = db.CreateTableDef("ExampleTable")

    With table1
        .Fields.Append.CreateField("newField", String)
    End With

But how could I adapt this to add the same field to a preexisting table?

Or more specifically, how do I modify

Set table1 = db.CreateTableDef("ExampleTable")

So that table1 points to an existing table in the db database?

Upvotes: 1

Views: 5320

Answers (2)

HansUp
HansUp

Reputation: 97101

You can add a field to an existing table by executing an Access DDL statement.

Dim strDdl As String
strDdl = "ALTER TABLE ExampleTable ADD COLUMN newField TEXT(255);"
CurrentProject.Connection.Execute strDdl

In TEXT(255), 255 is the field size --- the maximum number of characters that field can contain. And 255 is the absolute upper limit for an Access text field. When creating a TEXT field from the ADODB.Connection.Execute method, you must include a value for field size. Choose a smaller value if you prefer.

When you create a new field using DAO methods, as in your question, the field size for a text field is optional. However, when you don't specify a size, Access uses the "Default text field size" setting from your Access options.

Upvotes: 5

Dave Stuart
Dave Stuart

Reputation: 547

Something like this. Just reference the table name in the TableDefs collection rather than creating one.

Public Sub CreateTable()

Dim table1 As DAO.TableDef
Dim fld As DAO.Field
Dim tdf As DAO.TableDef

' Create the new Table Def object
Set table1 = New DAO.TableDef

' Name the Table
table1.Name = "Test"

' Create the new Field
Set fld = New DAO.Field
fld.Name = "newField"
fld.Type = DataTypeEnum.dbText

' Append the Field to the table
table1.Fields.Append fld

' Append the table to the Table Def Collection
' Without this the table just lives in memory
CurrentDb.TableDefs.Append table1

' Create another Field
Set fld = New DAO.Field
fld.Name = "newField2"
fld.Type = DataTypeEnum.dbText

' Append the New Field to the EXISTING table
CurrentDb.TableDefs("Test").Fields.Append fld

End Sub

Upvotes: 3

Related Questions