Add a Field in a Table Using Code

[MAB]

Is there a way a field can be added to a table when a command button is
clicked on a form? I know how to add records, delete records and update data
in the table in code, but can you actually add a field without being in
table design mode? My user has a table that comes to him with data in the
existing fields already. He uses a form and the code in the form to check
the data in the tables and to update certain fields before using the table
elsewhere. But along the way, he needs to add another field to the table
before it is useful to him. This field must be added after he gets the file,
since the existing application, (which makes the table in the first place),
cannot add the field in advance. Any help would be very much appreciated.

 

[Patrick]

Copy this code to the Click-event of your button :
Dim rsnew As Recordset
Dim dbs As Database
dim Sqlstring as String

' Insert new Product as a new column.
Set dbs = CurrentDb

SQLstring = "ALTER TABLE sizeList " _
& "ADD COLUMN MemName Bit " ' Bit is the Yes/No

dbs.Execute SQLstring

'SizeList' is the name of the table you wish to ADD a
column to and the 'Bit' is the type of the column.
Example: if you want the new column to be text then you
would write : ... MemName text(25). In Brackets, is the
Length of the field.

Hope this helps,
Patrick

 

[Patrick]

Sorry you wont need the rsnew,you can remove-it from you
code.