I have a table, 'Customer', which I want to use VBA/ADO to auto rename all
the fields. for example, to rename fields 1 to 'customer name', field to
'customer address' ,etc.

in Access, there's query 'Alter table, Alber column' but it only do for
changing column property, say set column as text/number, etc. I don't know
there's way to use SQL to rename a field in table.

I use Access2003 ANSI92. Thanks for any hints!

Warren
**************************************************
The Spoon Does Not Exist.

Hi:

One way to do this --

1. Use Alter Table SQL to add a new column
2. Use Update Query to copy data from old col to new column
3. Use Alter Table to delete old column.

However new column will not get any of the Indexes and Foeign Keys of the
old column.

Regards,

Naresh Nichani
Microsoft Access MVP

"sbcglobal" <(E-Mail Removed)> wrote in message
news:q2YNd.39300$(E-Mail Removed)...
> I have a table, 'Customer', which I want to use VBA/ADO to auto rename all
> the fields. for example, to rename fields 1 to 'customer name', field to
> 'customer address' ,etc.
>
> in Access, there's query 'Alter table, Alber column' but it only do for
> changing column property, say set column as text/number, etc. I don't know
> there's way to use SQL to rename a field in table.
>
> I use Access2003 ANSI92. Thanks for any hints!
>
> Warren
> **************************************************
> The Spoon Does Not Exist.
>
>

thanks, Nichani. the thing is I have too many fields to rename (about 40). I
hate to rename it in Excel and then import. I want to use some code to
import the table and rename fields at same time. or import table first, then
rename all fields at one time.

Basically I have 2 'group' of field names, one set for myself, another for
other users. so I want to 'switch' between these sets...ideas?

rgds,


"Naresh Nichani MVP" <nareshnichani@HOT MAIL> wrote in message
news:(E-Mail Removed)...
> Hi:
>
> One way to do this --
>
> 1. Use Alter Table SQL to add a new column
> 2. Use Update Query to copy data from old col to new column
> 3. Use Alter Table to delete old column.
>
> However new column will not get any of the Indexes and Foeign Keys of the
> old column.
>
> Regards,
>
> Naresh Nichani
> Microsoft Access MVP
>
> "sbcglobal" <(E-Mail Removed)> wrote in message
> news:q2YNd.39300$(E-Mail Removed)...
>> I have a table, 'Customer', which I want to use VBA/ADO to auto rename
>> all
>> the fields. for example, to rename fields 1 to 'customer name', field to
>> 'customer address' ,etc.
>>
>> in Access, there's query 'Alter table, Alber column' but it only do for
>> changing column property, say set column as text/number, etc. I don't
>> know
>> there's way to use SQL to rename a field in table.
>>
>> I use Access2003 ANSI92. Thanks for any hints!
>>
>> Warren
>> **************************************************
>> The Spoon Does Not Exist.
>>
>>
>
>

I'd use an Import Specification, but that might not meet your needs either.

"sbcglobal" wrote:

> I have a table, 'Customer', which I want to use VBA/ADO to auto rename all
> the fields. for example, to rename fields 1 to 'customer name', field to
> 'customer address' ,etc.
>
> in Access, there's query 'Alter table, Alber column' but it only do for
> changing column property, say set column as text/number, etc. I don't know
> there's way to use SQL to rename a field in table.
>
> I use Access2003 ANSI92. Thanks for any hints!
>
> Warren
> **************************************************
> The Spoon Does Not Exist.
>
>
>

On Tue, 08 Feb 2005 06:37:05 GMT, "sbcglobal" <(E-Mail Removed)>
wrote:

>Basically I have 2 'group' of field names, one set for myself, another for
>other users. so I want to 'switch' between these sets...ideas?

Changing the structure (including field names) of your database as a
routine operational procedure is never a good idea. Instead, use your
own names in the table, and have other users access the table via
queries that map their preferred names to the underlying ones, e.g.

SELECT ID, Fish AS Poisson, Meat AS Viande FROM MyTable;
SELECT ID, Fish AS Fisch, Meat AS Fleisch FROM MyTable;

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.