appending fields to tables

[jackal63]

I have a make-table query that generates a table called "TABLE" After the table is made, I would like to use the append method to add a field to the newly created table, called "EXTRA" that contains text. So the button code should look something like:

Button1 OnClick
docmd.runquery "MakeTABLE"
TABLE.append "EXTRA",text
end OnClick

I couldn't find anything usefull in the help section, does this sound right or am I missing something. Any help would be appreciated

 

[microdisney]

Are you trying to append records that conform to the field descriptions of the "MakeTable" ? or Are you trying to alter a table by adding an additional field ? If the latter is true, then you cannot accomplish this task using the append action query. Please explain more clearly.

 

[jackal63]

The latter, "EXTRA" is an entirely new field, not a record. According to this information I found on the DAO Append Method in help, It should work.

Append Method

Adds a new DAO object to a collection.

Syntax

collection.Append object

The Append method syntax has these parts.

Part Description
collection An object variable that represents any collection that can accept new objects (for limitations, see the table at the end of this topic).
object An object variable that represents the object being appended, which must be of the same type as the elements of collection.

Remarks

You can use the Append method to add a new table to a database, add a field to a table, and add a field to an index.

The appended object becomes a persistent object, stored on disk, until you delete it by using the Delete method. If collection is a Workspaces collection (which is stored only in memory), the object is active until you remove it by using the Close method.

The addition of a new object occurs immediately, but you should use the Refresh method on any other collections that may be affected by changes to the database structure.

 

[KyleS]

Jackal, you are on the right path. DAO is the way to go, just remember that in DAO they're not tables, they're TABLEDEFs from there help should be able to answer any questions you might have! Kyle :

 

[Quehay]

A more universal approach (not Access-specific) would be to use ALTER TABLE DDL (Data Definition Language)

Here's the Northwind example:

ALTER TABLE Statement Example
This example adds a Salary field with the data type Money to the Employees table.

Sub AlterTableX1()


Dim dbs As Database


' Modify this line to include the path to Northwind

' on your computer.

Set dbs = OpenDatabase("Northwind.mdb&quot


' Add the Salary field to the Employees table

' and make it a Money data type.

dbs.Execute "ALTER TABLE Employees " _

& "ADD COLUMN Salary MONEY;"


dbs.Close


End Sub