Change of fieldnames

[Thomas Olsen]

Hi

Can anybody tell me how to change the names of every field
in a table, by writing something like.:

"docmd.rename" in the procedure. I am not really into that
but it would be nice to change the names of fields for the
looks of the table and to make it easier to write a query.

Anybody ?

Thomas

 

[Jeff Boyce]

Thomas

I'm having a little trouble imagining a situation in which I'd want to
change all of a table's field names. Could you describe what you are trying
to accomplish, rather than how?

Actually, I can imagine one circumstance -- if I had a table that contained
field names that were "intelligent" (NOT a compliment!). For example, if my
field names were:

200301
200302
200303
...
200312

a reasonable supposition is that these fields represent the months of the
year 2003. Embedding this kind of meaning in "repeating" field names is
poor relational design (?non-relational design) and causes horrible
headaches when using Access.

Your post didn't mention this, but will you also be changing all the code,
queries, forms, reports and macros that refer to those field names?

Good luck!

Jeff Boyce
<Access MVP>

 

[Allen Browne]

If you must do this, it may be easiest to create a query that aliases all
your fields with the new names, and then change it into a MakeTable query
(Make Table on Query menu). If necessary, you could programmatically
generate and execute a SQL string that does the same thing.

The real question, though, is why this would be useful. if you have the kind
of field names that could justify a loop (e.g. Week1, Week2, Week3, ...) you
have a non-normalized data structure, and it would be better to break the
table down and use a related table. The analyzer should help you identify
where to go with that (Tools menu).

 

[Thomas Olsen]

-----Original Message-----
Thomas

I'm having a little trouble imagining a situation in which I'd want to
change all of a table's field names. Could you describe what you are trying
to accomplish, rather than how?

Actually, I can imagine one circumstance -- if I had a table that contained
field names that were "intelligent" (NOT a compliment!). For example, if my
field names were:

200301
200302
200303
...
200312

a reasonable supposition is that these fields represent the months of the
year 2003. Embedding this kind of meaning in "repeating" field names is
poor relational design (?non-relational design) and causes horrible
headaches when using Access.

Your post didn't mention this, but will you also be changing all the code,
queries, forms, reports and macros that refer to those field names?

Good luck!

Jeff Boyce
<Access MVP>

.
Hi Jeff,

The thing is that I am not that good using Access, but I
have this import module and a VB code for importing a file
in this case an .xls file which is causing me some
problems. What i need to do is to trim this file and get
rid of all the not used fields and rename the fields i
want to use (the rest of the fields) to another name. the
fields are now named F1, F2 ..... F38. I need to use only
22 of these fields and therefore i need to remove 16
fields which in any case is almost empty, some idiot ( not
you ) have written something in these colums in the . xls
file. I need to rename the fields to know which fields i
am using in my queries and forms and reports. Should i
send the .mdb to you and maybe you could help me....

Thanx for your support.
Thomas

 

[Thomas Olsen]

Hi Allen,

If I knew how to make this SQL string I would have done
it.

Could you give me some example of what to do... For the
moment I have a lot of code made for the same
commandbutton which has to do all the work. so please if
you send me an SQL og VB code please make it easy to put
into this ...

Thanx and regards
Thomas Olsen

 

[Allen Browne]

Let's take an example where you want to change the Surname field to LastName
and the FirstName field to ChristianName.

1. Create a query into your table.

2. Drag the Surname field into the grid.

3. Type:
LastName:
in front of Surname, so it reads:
LastName: [Surname]

4. Follow the same approach for the other fields you wish to alias.

5. Change the query to a Make Table query: Make Table on Query menu.

6. Execute the query.

You just created another table with the same structure but the new field
names.

To take this into code, switch your query to SQL view (View menu), and use
that as an example of the string you need to create in VBA to achieve the
same result. When you have built your string (named strSQL in this example),
this line of code will run it:
dbEngine(0)(0).Execute strSQL, dbFailOnError