Source language: Translate to:

sql to rename column in table

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

sql to rename column in table

Postby BRobinsonS » Tue May 26, 2015 7:25 am

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: sql to rename column in table

Postby Gaev » Tue May 26, 2015 8:58 am

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]
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: sql to rename column in table

Postby stu » Tue May 26, 2015 9:39 am

@Gaev, the statement looks about right.

What database are you using?
User avatar
stu
 
Posts: 320
Joined: Wed Aug 07, 2013 11:37 am

Re: sql to rename column in table

Postby BRobinsonS » Tue May 26, 2015 9:42 am

Just got your post while answering.

Using AccessDB, need to get better at SQL.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: sql to rename column in table

Postby BRobinsonS » Tue May 26, 2015 9:44 am

somehow lost the previous post to Gaev.

:(
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: sql to rename column in table

Postby Gaev » Tue May 26, 2015 11:31 am

@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
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: sql to rename column in table

Postby stu » Tue May 26, 2015 11:50 am

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...
User avatar
stu
 
Posts: 320
Joined: Wed Aug 07, 2013 11:37 am

Re: sql to rename column in table

Postby BRobinsonS » Wed May 27, 2015 2:03 pm

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 ?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron