Page 1 of 1

sql to rename column in table

PostPosted: Tue May 26, 2015 7:25 am
by BRobinsonS
I am trying to execute the following statement using Text box [SqlBox] to enter sql statement.

dbpExecSQL "[DatabaseName]" "[SqlBox]" "[TableName]" orginally dbpExecSQL "[DatabaseName]" "[SqlBox]" "temp1"

Sql Box contains
ALTER TABLE awards
RENAME COLUMN owner_name to ownername;


I get either 'cannot perform operation on closed dataset' or error in sql statement.
The app has opened the table for viewing in a grid.
I tried using variable [TableName] in place of 'awards' (the actual name of the table).

Help would be appreciated.

Re: sql to rename column in table

PostPosted: Tue May 26, 2015 8:58 am
by Gaev
Brian:

The help section for the dbpExecSQL command says ...
SQL

The SQL commands to execute. As required by SQL conventions, multiple commands must separated with semicolons (;).

... looks like your content has commands separated by [#13][#10]

Re: sql to rename column in table

PostPosted: Tue May 26, 2015 9:39 am
by stu
@Gaev, the statement looks about right.

What database are you using?

Re: sql to rename column in table

PostPosted: Tue May 26, 2015 9:42 am
by BRobinsonS
Just got your post while answering.

Using AccessDB, need to get better at SQL.

Re: sql to rename column in table

PostPosted: Tue May 26, 2015 9:44 am
by BRobinsonS
somehow lost the previous post to Gaev.

:(

Re: sql to rename column in table

PostPosted: Tue May 26, 2015 11:31 am
by Gaev
@Gaev, the statement looks about right.

Only if it was presented as ...
ALTER TABLE awards RENAME COLUMN owner_name to ownername;
... i.e. all on one line


@Brian:

I did a Google search on "access sql alter table rename column" ... surprise, surprise, looks like Access does NOT support the RENAME option.

You will have to do it in steps ...

a) do an ADD COLUMN

b) copy all content from current column to new column

c) do a DROP cOLUMN on the old column

Re: sql to rename column in table

PostPosted: Tue May 26, 2015 11:50 am
by stu
Gaev wrote:
@Gaev, the statement looks about right.

Only if it was presented as ...
ALTER TABLE awards RENAME COLUMN owner_name to ownername;


Isnt NB supposed to add the pipes to indicate a new line? :?:

Anyway... I just try to throw an ALTER to an Access sample db and I'm getting an error, this could be due to Access being absolute crap... there's ways of playing around with ALTER from VBA but I assume that's out of the question...

Re: sql to rename column in table

PostPosted: Wed May 27, 2015 2:03 pm
by BRobinsonS
Gaev,

Now you mention it. I may have asked this question before and the answer was 'Rename' is not supported with AccessDb.

I did look at some of my coding and I had accomplished the 'rename' in separate steps you suggested
i.e. Add a column/filed, Copy contents from the old column/field to the new column/field and Drop the original column/field.

I guess it would be simple enough to combine the steps into one procedures.

I am guessing (haven't tried it yet) that this technique could be used to 'resize' the column/field too ?