Source language: Translate to:

Renaming Tables

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Renaming Tables

Postby BRobinsonS » Sat Sep 08, 2012 12:33 pm

From the Help file
Example:
dbpExecSQL "DB1" "RENAME TABLE Sales TO Orders" ""


Couldn't get it working with MDB file. I was using Variables.

dbpExecSQL "[DatabaseName]" "RENAME TABLE [TableName] TO [NewTableName]" ""

Doesn't recognize RENAME said use UPDATE, PROCEDURE, SELECT etc.

Used this to accomplish the task:

dbpExecSQL "[DatabaseName]" "SELECT * INTO [NewTableName] FROM [TableName]" ""
AlertBox "New Table" "Created [NewTableName]. Delete Old Table."

Ideas?
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: Renaming Tables

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

BRobinsonS wrote:From the Help file
Example:
dbpExecSQL "DB1" "RENAME TABLE Sales TO Orders" ""


Couldn't get it working with MDB file. I was using Variables.

dbpExecSQL "[DatabaseName]" "RENAME TABLE [TableName] TO [NewTableName]" ""

Doesn't recognize RENAME said use UPDATE, PROCEDURE, SELECT etc.

Used this to accomplish the task:

dbpExecSQL "[DatabaseName]" "SELECT * INTO [NewTableName] FROM [TableName]" ""
AlertBox "New Table" "Created [NewTableName]. Delete Old Table."

Ideas?
Access databases seam to be limited with what SQL commands will work.
I'm not very familiar with SQL commands so one way to do it is adding a new table first then import all the records from the old table before deleting it:
Code: Select all
dbpCreateTable "TableID" "NewTableName" "String1 String(35) PrimaryKey"
dbpExportToCSV "TableID" "OldTableName" "ExportFile.csv" "Range=All;Delimiter=,;IncludeHidden=Yes;IncludeFieldNames=Yes;UseColumnTitles=No;Append=No;ForceQuotes=No"
dbpOpenTable "TableID" "NewTableName" ""
dbpImportFromCSV "TableID" "NewTableName" "ExportFile.csv" "Delimiter=,;ContainsFieldNames=Yes;MatchFieldNames=Yes"
dbpDropTable "TableID" "OldTableName"

You might consider running a Loop sequence to be sure all records were imported correctly before dropping the old table, but from my experience it works fairly well. Also be sure to always us a primary key!
smokinbanger
 
Posts: 198
Joined: Mon Jan 16, 2012 9:53 am
Location: United States

Postby Gaev » Sat Sep 08, 2012 7:55 pm

Brian:

NeoBookDBPro has built in commands for adding (dbpAddField) and deleting (dbpDeleteField) fields within a Table ... I use them in my AccessDBU utility program.

So, all you need to do is ...

1) add a field

2) loop through all records and copy the contents of the current field to the newly added field

3) delete the older field

Of course, you would backup the entire database file before attempting such irreversible actions.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

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

I guess this is the answer to RENAME field.
Looks like this is the technique to use.

Thanks Gaev.
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 5 guests