Renaming a Column/Field in Access Table

james_sellwood · December 12th, 2003, 01:51 PM

Hi,

I am trying to rename a column/field in an Access Database Table from within an ASP.NET page. I am aware that it would be much simpler to rename the column/field from within Access itself but the database is in use and to download it would cause unneccessary down time for what I think should be possible in code. (I am aware of the problems with renaming columns on tables that are in use, that is not a problem as this is a table that is not yet in use.)

I have come accross these bits of SQL / Access code:

Code:

ALTER TABLE tableName RENAME COLUMN oldColumnName TO newColumnName

and

Code:

Tabledefs('tableName').Fields('oldCoumnName').Name = 'newColumnName'

These may work in some way but from within my ASP.NET page I am unable to actually modify the table. I have been trying to use myCommand.ExecuteNonQuery() with the above code but I could not get this to work. I also tried using the above code as part of an OLEDB Update Command but have been unsuccessful.

I would appreciate any help that anyone can provide in pointing me in the right direction.

Thanks

--------------------
:) James Sellwood :)
--------------------

webtekkie74 · February 3rd, 2005, 05:34 PM

Try this:
  Dim conn as New ADODB.Connection
  Dim ctlg as New ADOX.Catalog

  conn.Open "<your params>"
  ctlg.ActiveConnection = conn

  Dim tbl_name As String
  Dim old_col_name As String
  Dim new_col_name As String

  tbl_name = "SOMETABLE"
  old_col_name = "SOME OLD COLUMN"
  new_col_name = "SOME_NEW_COLUMN"

  ctlg.Tables(tbl_name).Columns(old_col_name).Name = new_col_name

  conn.close
  Set conn = nothing

Hope this helps
- webtekkie74

mehdi62b · February 4th, 2005, 07:39 AM

I would not recommend you to use an ASP.NET app,you can simply do it with a simple windows app instead,anyway for changing column name use this SP in master database,

Code:

EXECUTE sp_rename N'TableName.OldColumnName', N'NewColumnName', 'COLUMN'

as you see it's better to use a simple windows app instead because of security risks,

Quote:

quote:Originally posted by james_sellwood
I also tried using the above code as part of an OLEDB Update Command but have been unsuccessful.

no,it's just for simple Update sql statements.

_____________________________
Mehdi.
software engineering student.
Looking for a good job for summer 2005.

mehdi62b · February 4th, 2005, 08:45 AM

I forgot to post its code,

Code:

string strConnection=""//according to your DB connection
SqlConnection objConnection=new SqlConnection(strConnection);
SqlCommand objCommand=new SqlCommand("sp_rename",objConnection);
objCommand.CommandType=System.Data.CommandType.StoredProcedure;
objCommand.Parameters.Add("@objname","[table_name].[current_name]");
objCommand.Parameters.Add("@newname","[new_name]");
objCommand.Parameters.Add("@objtype","COLUMN");
objConnection.Open();
objCommand.ExecuteNonQuery();
objConnection.Close();

Attention:all of that was for SqlServer/MSDE

_____________________________
Mehdi.
software engineering student.
Looking for a good job for summer 2005.