Help in renaming multiple fields using VBA - calling all VBA gurus!

Question

How do I rename multiple fields in an Access table (Table1), using the data sourced from another table (Table2) which has 2 columns storing the "old name" and "new name". My intent is to have each new name replace the original field name in Table1.

More specifically:

The Table 1 stores survey data, with field names like "Q1_a".

I have created Table2 with a field name called: "old name", and I have populated this with the original field names which are survey question codes (eg. Q1_a, Q2_a, Q2_b etc); the second field in Table2 is named: "new name", and I have typed in the corresponding description to the question codes (eg. Question 1a, Question 2a, Question 2b etc).

Now I would like to use VBA to update the field names - I am quite new to VBA, but am able to get by, with some guidance.

Thanks in anticipation .......

Answer 1

Why rename them at all?  You can return the data as the result set of a query which aliases each column:

SELECT

    Q1_a AS [Question 1a],

    Q2_a AS [Question 2a],

    Q2_b AS [Question 2b],

    < and so on>

FROM [Table 1];

If you do want to rename them then insert the result set of this query into a new table using a 'make table' query.

But a far better solution would be to recast the table into a set of normalized tables by first inserting rows into an empty Questions table, one for each question, using the names you'd give to each column in the above query's result set as the values.  This would then be followed by inserting rows into a Responses table using a UNION ALL operation:

INSERT INTO Responses(Question, Response)

SELECT "Question 1a", Q1_a

FROM [Table 1]

UNION ALL

SELECT "Question 2a", Q2_a

FROM [Table 1]

UNION ALL

SELECT "Question 2a", Q2_b

FROM [Table 1]

UNION ALL

< and so on>;

The Questions and Responses table can them be related on the Question columns, enforcing referential integrity and cascade updates.

If the responses are a standardized set of fixed answers you can also insert rows into an Answers table, one row per answer value, which you can ether do manually or again by means of an 'append' query from the original table, making sure that the Answer column is designated as the primary key so that duplicate values are rejected:

INSERT INTO Answers(Answer)

SELECT  Q1_a

FROM [Table 1]

UNION ALL

SELECT Q2_a

FROM [Table 1]

UNION ALL

SELECT Q2_b

FROM [Table 1]

UNION ALL

< and so on>;

This would be related to Responses on the Answer columns, again enforcing the relationship.

In each of the 'append' queries above you do not have to do it all in one massive UNION ALL operation; it can be done piecemeal, appending from a subset of the original table's columns each time.

Answer 2

Appreciate your advice, BUT unfortunately I am stuck with the data the way it is (due to funding, and the fact there are over 300 field names) - I have the raw data which was provided to me by an external body who actually did the survey. They did a generalised report, and had the raw data available for anyone who was interested in interrogating the data to a greater level of detail. Yes, this is me. So still looking for an answer to my question .......

Answer 3

It looks like you are trying to solve a bad design with more bad design. A survey database has a very specific structure. And that structure Is NOT to have a field for each question. Check out the link Doug gave you for proper survey design.

Answer 4

No offense, but DON'T DO IT!

Seriously: having fields with names like Q1_a, Q2_a is a bad idea: it makes queries much, much harder.

Take a look at Duane Hookom's At Your Survey for how to design a proper survey database.