Automatically creating new fields in Access using Macros

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";

Hi. Welcome to the forum. :wave:

You can create a Data Definition query:

Code:

---

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

---

and populate it in one update query like this:

Code:

---

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?

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

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.