• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

Excel VBA RENAME SQL COLUMN

Hi

I am trying to rename a SQL Column in Excel VBA using the following statement but it says incorrect syntax

 sSQL = "ALTER TABLE [BANK] RENAME COLUMN [ACOUNT] to [ACCOUNT]"
0
Murray Brown
Asked:
Murray Brown
  • 3
  • 3
  • 2
  • +1
1 Solution
 
MacroShadowCommented:
The syntax seems to be correct. Try
sSQL = "ALTER TABLE BANK RENAME COLUMN ACOUNT to ACCOUNT;"
0
 
PortletPaulCommented:
believe you need to use sp_rename

EXEC sp_rename
    @objname = 'BANK.ACOUNT ',
    @newname = 'ACCOUNT',
    @objtype = 'COLUMN'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Playing with fire, we are...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MacroShadowCommented:
Let's hope this is the beginning of the developing....
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi MacroShadow I get the error Incorrect syntax near 'RENAME'.
0
 
PortletPaulCommented:
I am NOT aware of RENAME being available in TSQL. Refer to the documentation page for ALTER TABLE
Try using the browser's search (ctrl f) for "rename"

The syntax is not correct for SQL Server
(it might be for MySQL for example)

Consider the following which you can try yourself here: http://sqlfiddle.com/#!3/414b0/1
CREATE TABLE BANK
     ([ID] int identity primary key
     
     , [ACOUNT] int                  --<< incorrect spelling
    );

INSERT INTO BANK 	([ACOUNT])
VALUES 	(10),	(20),	(30);

                                      --<< fix the spelling!

EXEC sp_rename @objname = 'BANK.ACOUNT ', @newname = 'ACCOUNT', @objtype = 'COLUMN';

Open in new window

-- note the column heading
SELECT * FROM BANK;


-- now try this syntax:
ALTER TABLE BANK RENAME COLUMN ACCOUNT to ACOUNT;

Open in new window

| ID | ACCOUNT |
|----|---------|
|  1 |      10 |
|  2 |      20 |
|  3 |      30 |
		
Incorrect syntax near 'RENAME'.: -- now try this syntax:
ALTER TABLE BANK RENAME COLUMN ACCOUNT to ACOUNT

Open in new window

0
 
MacroShadowCommented:
0
 
PortletPaulCommented:
That reference is for "generic SQL" NOT T-SQL (which  is the MS SQL Server dialect of SQL)

Microsoft do not own the term "SQL" although I do know many folk will just use that term when referring to "Microsoft SQL Server" so it's easy to get this wrong when out of context.

The definitive reference for T-SQL is "Books On-Line" (BOL)
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now