Adding a column to multiple tables in MS Access using SQL

Question

I have an Access database with almost 200 tables. I would like to rename the fields of many of these (to make it easier when I analyze the data in R). I've discovered that you can't simply rename fields in Access, but have to do it the hard way of adding a new column, transferring data, then dropping the old column.

Is there a way to do this for multiple tables (or multiple tables and columns) at once using SQL?

I tried this:

ALTER TABLE * FROM MSysObjects ADD COLUMN colname NUMBER

as well as this:

ALTER TABLE table1, table2, table3... ADD COLUMN colname NUMBER

but neither seem to work.

Is this possible at all? Does anyone know how to code this?

HansUp · Accepted Answer · 2013-11-05 18:34:48Z

2

"Is there a way to do this for multiple tables (or multiple tables and columns) at once using SQL?"

No. That is beyond the capability of Access SQL. You will need to execute a series of statements, and each can only alter a single table.

You could create a VBA procedure to build and execute those statements. But, if you're willing to use VBA, consider simply renaming the columns via the DAO object model.

Here is a simple example from the Immediate window:

? CurrentDb.TableDefs("tblFoo").Fields("some_text").Name
some_text
CurrentDb.TableDefs("tblFoo").Fields("some_text").Name = "some_text2"
? CurrentDb.TableDefs("tblFoo").Fields("some_text2").Name
some_text2

edited Nov 5, 2013 at 18:34

answered Nov 5, 2013 at 16:42

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

logicForPresident Over a year ago

Thanks, Hans. Is it possible to do what I want in an actual SQL program (e.g. MySQL, SQLite)?

HansUp Over a year ago

Perhaps, but I can't offer any help for those.

Gord Thompson Over a year ago

@Paul re: "Is it possible to do what I want in [some other database]?" - Generally speaking, no. At least I'm not aware of any databases that offer a "global update" feature to change the structure of database objects. (By the way, remember that you can accept an answer if you find it helpful. Accepted answers help others recognize that your question has been resolved.)

Collectives™ on Stack Overflow

Adding a column to multiple tables in MS Access using SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related