Reputation: 4556
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
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
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