Source language: Translate to:

Rename Column/Field

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Rename Column/Field

Postby BRobinsonS » Sat Sep 08, 2012 5:17 pm

Is it possible through dbpExecSQL to rename a Table Column/field for Microsoft Access Database (MDB)?

I don't see a NeobookDBPro command for this.

Tried various statements without success.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Rename Column/Field

Postby smokinbanger » Sat Sep 08, 2012 7:12 pm

BRobinsonS wrote:Is it possible through dbpExecSQL to rename a Table Column/field for Microsoft Access Database (MDB)?

I don't see a NeobookDBPro command for this.

Tried various statements without success.
No it's not. Access databases are limited in their support for SQL commands.
smokinbanger
 
Posts: 198
Joined: Mon Jan 16, 2012 9:53 am
Location: United States

about rename a field

Postby carlos torres » Sat Sep 08, 2012 7:33 pm

dear BRobinsons
I think that you can create a new field with the name you want, populate it then delete the old field
Regards

Carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Postby BRobinsonS » Sun Sep 09, 2012 5:36 am

thanks for the information.

I have found a utility on the net called MDBPlus which works with Access DB files which does things like rename field.

Will do this:
I think that you can create a new field with the name you want, populate it then delete the old field


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

Postby BRobinsonS » Sat Sep 15, 2012 10:35 am

I seem to be have trouble copying/populating the new field with old field with the purpose of renaming a field by deleting the old.
Problem might be addressing the field through variables:
e.g. SetVar "[[DatabaseName].[TableName].[ToField]]" "[[DatabaseName].[TableName].[FromField]]"

I use a Custom Windows to enter FromField e.g. Phone and ToField e.g Fax then I've tried While and Loop without success. Sometimes the new field contains data but nothing like the old field. Both are String(20) fields.

Code I am working with below (action on Ok button). Suggestions?

dbpFieldExists "[DatabaseName]" "[TableName]" "[FromField]" "FromFieldExist"
dbpFieldExists "[DatabaseName]" "[TableName]" "[ToField]" "ToFieldExist"
IfEx "[FromFieldExist] = True AND [ToFieldExist] = True"
dbpFirst "[DatabaseName]" "[TableName]"
AlertBox "First Field" "[[DatabaseName].[TableName].[FromField]]"
. while to copy content to another field
SetVar "[StartNum]" "1"
SetVar "[EndNum]" "[[DatabaseName].[TableName].$RecCount]"
AlertBox "Record Count" "[EndNum]"
.While "[StartNum]" "<=" "[TableName].$RecCount"
. While "[StartNum]" "<=" "[EndNum]"
. AlertBox "StartNum" "[StartNum]"
Loop "1" "[[DatabaseName].[TableName].$RecCount]" "[Counter]"
SetVar "[#34][[DatabaseName].[TableName].[ToField]][#34]" "[[DatabaseName].[TableName].[FromField]]"
dbpNext "[DatabaseName]" "[TableName]"
. AlertBox "Next Field" "[[DatabaseName].[TableName].[FromField]]"
. SetVar "[StartNum]" "[StartNum] + 1"
EndLoop
. EndWhile
CloseCustomWindow "CopyDialog"
Else
AlertBox "Error" "Field [FromField] or [ToField] doesn't exist."
EndIf
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Postby Gaev » Sat Sep 15, 2012 11:43 am

Brian:

1) Using nested variables for referencing Database fields can be problematic ... in any case remove the [#34] wrappers ... not needed even with non-databse variables

2) While needs a EndWhile ... not EndLoop

3) If your Phone field has hyphens e.g. 123 456-7890 ... you need to use the Exclamation mark to prevent NeoBook from doing Math on it

If these changes don't work ...

First try (from a Button all by itself) ...
Code: Select all
dbpFirst "[DatabaseName]" "[TableName]"
SetVar "[StartNum]" "1"
SetVar "Endnum] "5"

While "[StartNum]" "<=" "[EndNum]"
   SetVar "[ThisFrom]' "![[DatabaseName].[TableName].Phone]"
   SetVar "[[DatabaseName].[TableName].Fax]" "![ThisFrom]"
   Setvar "[AlertMsg]" "![ThisFrom] to [[DatabaseName].[TableName].Fax]"
   AlertBox "[StartNum] of [EndNum]" "![AlertMsg]"
   dbpNext "[DatabaseName]" "[TableName]"
   SetVar "[StartNum]" "[StartNum] + 1"
EndWhile

If that works as expected, try ...
Code: Select all
SetVar "[FromField]" "Phone"
SetVar "[ToField]" "Fax"

dbpFirst "[DatabaseName]" "[TableName]"
SetVar "[StartNum]" "1"
SetVar "Endnum] "5"

While "[StartNum]" "<=" "[EndNum]"
   SetVar "[ThisFrom]' "![[DatabaseName].[TableName].[FromField]]"
   SetVar "[[DatabaseName].[TableName].[ToField]]" "![ThisFrom]"
   Setvar "[AlertMsg]" "![ThisFrom] to [[DatabaseName].[TableName].[ToField]]"
   AlertBox "[StartNum] of [EndNum]" "![AlertMsg]"
   dbpNext "[DatabaseName]" "[TableName]"
   SetVar "[StartNum]" "[StartNum] + 1"
EndWhile

Post your findings ... the values that got copied correctly as well as the ones that did not.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby BRobinsonS » Sun Sep 16, 2012 8:17 am

Thank you, Gaev.
You hit the nail on the head.

Post your findings ... the values that got copied correctly as well as the ones that did not.

I had it working and didn't recognize it. The key was I was using phone number 519-258-1234 and it was evaluating to -973 (doing the math).
The solution was using ! sign to copy literally. Now phone 519-258-1234 copies to Fax as 519-258-1234.

This code works for me now.
dbpFieldExists "[DatabaseName]" "[TableName]" "[FromField]" "FromFieldExist"
dbpFieldExists "[DatabaseName]" "[TableName]" "[ToField]" "ToFieldExist"
IfEx "[FromFieldExist] = True AND [ToFieldExist] = True"
dbpFirst "[DatabaseName]" "[TableName]"
SetVar "[StartNum]" "1"
SetVar "[EndNum]" "[[DatabaseName].[TableName].$RecCount]"
. while to copy content to another field
While "[StartNum]" "<=" "[EndNum]"
SetVar "[ThisFrom]" "![[DatabaseName].[TableName].[FromField]]"
SetVar "[[DatabaseName].[TableName].[ToField]]" "![ThisFrom]"
dbpNext "[DatabaseName]" "[TableName]"
SetVar "[StartNum]" "[StartNum] + 1"
EndWhile
CloseCustomWindow "CopyDialog"
Else
AlertBox "Error" "Field [FromField] or [ToField] doesn't exist."
EndIf


I now understand the use of ! character. Glad I picked the phone number to copy to discover this.
Now I can use this technique to basically rename a field.

Thanks again for your help.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
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