If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > How to rename the column name in MS Access table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2005
Posts: 313
How to rename the column name in MS Access table

Hi:

MS Access
In query:
I write

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

But, it doesn't work, because "Rename" is NOT avaliable.
How can I fix that?

Thanks.
Reply With Quote
  #2 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,273
look under TableDef in the help has some good exmples
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE
Reply With Quote
  #3 (permalink)  
Old
Stuck on my opinions...
 
Join Date: Nov 2003
Posts: 1,487
Just like anything else in Microsoft Access, you can do almost any one thing about six different ways. It all depends upon which environment or environments you want to play in. Here is a little function using ADOX that will do the task at hand:

NOTE:
You will need to Reference the Microsoft ADO Ext for DDL and Security (ADOX) Library.

Code:
Public Function RenameColumn(ByVal StrgDB_Name As String, StrgTableName As String, _
				StrgOldColumnName As String, StrgNewColumnName As String) As Boolean
   On Error GoTo Err_RenameColumn
 
   'Create a Catalog object
   Dim Cat As ADOX.Catalog
   Set Cat = New ADOX.Catalog
   Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & StrgDB_Name
 
   'Create a table object
   Dim Tbl As ADOX.Table
   Set Tbl = New ADOX.Table
   Set Tbl = Cat.Tables(StrgTableName)
   Tbl.Columns(StrgOldColumnName).Name = StrgNewColumnName
 
   'Return that the function was Successfull.
   RenameColumn = True
 
Exit_RenameColumn:
   'Clean up
   Set Cat = Nothing
   Set Tbl = Nothing
   Exit Function
 
Err_RenameColumn:
	MsgBox "There was an Error while Renaming the Table Column [" & StrgOldColumnName & "] to [" & _
	StrgNewColumnName & "] within the Table named [" & StrgTableName & "] which is located in the " & _
	StrgDB_Name & " Database.", vbCritical, "Column Rename Error"
	GoTo Exit_RenameColumn
End Function

.
__________________
Environment:
Self Taught In ALL Environments.....And It Shows!

Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2005
Posts: 313
Excuse me, Could you attach one example for me? If you don't want, it is fine. Forget about it.
I do NOT quit understand.
It's fine.
Thanks a lot. Thanks.
Reply With Quote
  #5 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,092
Quote:
Originally Posted by accessman2
Excuse me, Could you attach one example for me? If you don't want, it is fine. Forget about it.
I do NOT quit understand.
It's fine.
Thanks a lot. Thanks.
what precisely do you think Cyberynx has provided to you in code element of his posting?
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2005
Posts: 313
Hey, man. I already said "Forget about it". It is optional.
Doesn't matter. Please read "Forget about it".

Thanks.
Reply With Quote
  #7 (permalink)  
Old
Stuck on my opinions...
 
Join Date: Nov 2003
Posts: 1,487
Hmmmm...It almost appears that perhaps there is a slight language barrier here.

accessman2...if you can tell me what your native language may be, then perhaps I can translate the code for you. It may make it easier for you to understand. You see, I'm like the Pope. I can speak 15 different languages.

Or..is it that you do not understand VBA code very well? If this is the case, then I simply will not waste my time anymore creating the suggestive code (since I do almost everything with code in my applications).

Is it that I don't post enough comments in the code? If so (anyone) then please let me know.

If you would still like to have a sample application of the above code, I would be more than happy to accomodate you. That is if you really do want it.

sincerely,

.
__________________
Environment:
Self Taught In ALL Environments.....And It Shows!


Last edited by CyberLynx; 10-23-05 at 20:36.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Sep 2013
Posts: 1
thank you very much for this excellent code

however
i required this additional information
for a complete solution
a) comment out the line "On Error GoTo Err_RenameColumn" because this obscurs the actual error message
d) "To use this library, open the code window, choose References on the Tools menu, and check the box beside: Microsoft ADO Ext x.x for DDL and Security"
which i found browsing to C:\Program Files\Common Files\System\ado\msadox28.tlb
c) need a function like below to actually utilise the supplied code
--------------------------------------------------------------
Sub ren_cols()

Dim blah As Boolean
blah = RenameColumn("<path><filename>.mdb", "<tablename>", "<old_col_name>", "<new_col_name>")
End Sub
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On