Field rename query

[Slade2000]

I would like to rename fields with a query. I did do a search and came up with this: "ALTER TABLE "your_table"
RENAME COLUMN "old_field" to "new_field"

But where do i type this? or is this SQL? How do i do it in access?

Tnx allot.

Bob: sorry for all the questions....tnx for your help.

 

[RossWindows]

in Access if you have a field called "Field1" and you want the query to display it as "Field2" then in the query builder,
just type "Field2: [Field1]" (without the quotes)

The picture can probably explain better than I can.

 

[Slade2000]

I want it to actualy rename a table field coz i import a text file then it name it field 1,2 and 3 that is the 3 imported fields and i give it a table name (table2)then i want to rename them te be the same as table1 that is maybe name,surename,tel. coz i use a append to add the imported data from table2 to be added to table1 but it does not want to append if my field names are not exacly the same.

 

[RossWindows]

Oh, I see. I misundermeant what you stood.

 

[Slade2000]

LOL...maybe a update query?

 

[RossWindows]

I believe an update query will only update the data within a table;
Here is something I found in the Access '03 Help:

I think Access '03 only supports the following SQL definitions (It's probably similar for other versions too)
So, unless "ALTER TABLE" works, you might be out of luck.

CREATE TABLE Creates a table
ALTER TABLE Adds a new field or constraint to an existing table
DROP Deletes a table from a database or removes an index from a field or group of fields
CREATE INDEX Creates an index for a field or group of fields

 

[Slade2000]

Yup i have seen the "alter table" but dont know where to type it in to see if it will work.

 

[RossWindows]

Ok, attached is an image from the Help.
This is how you run a SQL Specific query

As for your particular task, I haven't had a chance to slice-and-dice this, but here is the full syntax of an ALTER TABLE or ALTER COLUMN query:

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

 

[Slade2000]

Tnx allot let me try.

 

[RossWindows]

If none of that works, you may need to change the data definition by adding a column with the column name you want, copying your original column's data to the new column, and then you could drop the original column from the table that was altered.

or, use VBA

http://www.access-programmers.co.uk/forums/showthread.php?t=173948&highlight=alter+column

 

[Slade2000]

can i just copy that into a button? if so how would it look like. I understand a bit of VB

 

[RossWindows]

I believe you would copy it into a new standard module, then if you want a button to run the code, then put the following into the button's on-click event

RenameField "tblRenCol","Jeff","Jeffrey"

 

[georgedwilkinson]

You can actually run DDL (Data Definition Language) like this in a standard query window. Just define a new query, go into SQL mode, and type in the DDL. Press go (exclamation point?) to go.

 

[Slade2000]

ok i added it to a module and insert a button on a form and it calls the module. if my field are named field1 field2 and field3 what should i then change in the code in the module then?

 

[RossWindows]

RenameField "table_name","old_column_name","new_column_name"

 

[Slade2000]

Got it to work tnx allot