Read column name with VBA

[Webkins]

I have a Access 2003 database table with 12 columns. Each column name is a date and is constantly changing each day. For example:

the name of column 1 is: 2010/03/27
the name of column 2 is: 2010/02/27
the name of column 3 is: 2010/01/27
the name of column 4 is: 2009/12/27
the name of column 5 is: 2009/11/27

and so on.

I need to be able to read the column name so that I rename it to something else. Is this possible with VBA ? Thank you for any and all assistance with this problem.

 

[SkipVought]

Hi,

Your db design is so wrong. Violates several best and accepted practices.

How entrenched are you in this db structure?

Please explain WHY the field names change.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Concur with Skip. That sounds like a real bad idea. Not worth the effort trying to do what you are asking. Redesign your database.

 

[dhookom]

This could be the result of a linked table from Excel or from another system that Webkins has no control over. I agree it sounds like a poorly designed table structure for many reasons but I would rather not jump to conclusions too quickly.

You can use DAO code to loop through the Fields collection of the TableDef. This would require a reference to the DAO object library. If you can't resolve your issue, post back and be prepared to defend/explain your columns.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

You may play with the Fields collection of a TableDef object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya Webkins . . .

[blue]I need to be able to [purple]read the column name so that I rename it to something else.[/purple][/blue]

Give us more info on the names your changing too! If the name will be common ... great! ... If not ... trouble. Especially if you intend to write code!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Golom]

The implication of changing column names in code is that you will have a table (or tables) whose columns have unpredictable names. That means that you cannot have static SQL to extract or modify data in them and you will need to pull the column names from recordsets to have any notion of what you are looking at.

Effectively you are turning meta-data (i.e. information about the table) into variable data which is what is normally stored in the table.

If you can describe why you want to do this, perhaps we can suggest less suicidal ways of achieving your goal.

 

[TheAceMan1]

Webkins . . .

In sync with [blue]Golom[/blue] ... [blue]we need a critique on what your changing fieldnames too![/blue] ... it would explain alot!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]