Add a field using VBA 2

[simonjackson79]

Hi,

I have a macro setup up that imports a text file "All CLA ever.txt" and creates a table from it. Is there anyway that I can add a text field called "Key Worker" to the table using VBA?

Many thanks in advance,

Simon

 

[PHV]

Take a look at the ALTER TABLE DDL instruction, like this:
DoCmd.RunSQL "ALTER TABLE myTable ADD COLUMN [Key Worker] TEXT(25)"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[scriverb]

Try this code example:

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As Field
Set db = CurrentDb
Set tbl = db![red]yourtablename[/red]
Set fld = tbl.CreateField("Key Worker", DB_TEXT)
fld.Size = 2
tbl.Fields.Append fld
db.close

There are other properties of the new text field that can be set like the code line above for Size. (i.e. Default, Ordinal Position, etc.) You can see these properties in ACCESS Help and add them as necessary.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[snakehips2000]

Hi Simon,

You cannot programmatically add a field to a table once the table has been created and added to the Tabledefs collection.

If you say your existing macro creates the table, I would suggest you insert some code within your macro using the RunCode command which first creates a tabledef object, then create field objects for the field(s) you wish to have in the table (CreateField method), then adds each of those field objects to the tabledef object, and finally adds the tabledef object to the Tabledefs collection.

 

[simonjackson79]

Thanks Bob, that worked a treat, just what I needed!

Si