Automatically creating new fields in Access using Macros

[rbalding]

Hi,

I am relatively new to Access so play nice...

I have a requirement to import regular data into Access and then do a series of queries on the data. I have got quite a long way with it but I am stuck on the following;

I need to add some fields to a table (PageExtent, Runcode and MailsortTrigger) and then populate these fields with data. I can't find any other way apart from manually adding the fields to the table first then running the update queries as individual queries. Is there a better way of doing this?

Thanks in advance...

Richard

UPDATE table SET PageExtent = "E18";

UPDATE table SET RunCode = "testR";

UPDATE table SET table.MailsortTrigger = "0";

[PilgrimPete]

Hi. Welcome to the forum.

You can create a Data Definition query:

ALTER TABLE theTable 
ADD PageExtent TEXT(20), 
  RunCode TEXT(20), 
  MailSortTrigger SMALLINT;

and populate it in one update query like this:

UPDATE theTable 
SET PageExtent = "E18", RunCode = "testR", MailSortTrigger = 0 
WHERE theKey = "something";

I'm not sure why you need to add columns dynamically at run-time though. That doesn't necessarily sound like the best solution. Can you tell us any more about what you are trying to achieve?

[leinad31]

DDL have implicit COMMIT, it's not advisable to mix DML and DDL.

[RobDog888]

If its only the 3 fields then it would be better to manually create them as you dont want it to run in a macro possibly a second time etc.