Edit Table Name

Hi Xojo Support Team

is it possible to edit the name of a Table without having to create a new table and copy the data from the old table to the new table and delete the original table name?

If there is a way it would be great to know how to do it.

Thank you

Something like:

myDatabase.SQLExecute("ALTER TABLE TableName RENAME TO NewTableName;")

?

Depends on the database. It’s not a Xojo question. Find the right syntax for your database and use sqlexecute like Jason points out.

Thanks guys. I am using an ODBC Database and this is the code I am using. Would you mind taking a look to see if this is the correct syntax…

Dim db As New ODBCDatabase
db.DataSource = datbasename
// oldname and newname are strings
if db.connect then
  db.sqlexecute("ALTER TABLE '" + oldname + "' RENAME TO '" + newname +"';")
end if
if db.error then
  msgbox(db.errormessage)
end if

Appreciate your help.

As Tim said, it will depend on the type of database. ODBC is not a type of database — it’s a way of connecting to databases. It would help if you advised the type of database that you are connecting to.

My code was for SQLite. I can see a problem with your SQL string: you’ve used single quote marks around the table names. If you take a look at my sample code, these are not required (at least for SQLite).

Thanks Jason. I am connecting to a Microsoft Access Database *.mdb. Does this help?

Thank you

It’s been a while since I’ve used an MDB but I think my code might work.

So just replace:

db.sqlexecute("ALTER TABLE '" + oldname + "' RENAME TO '" + newname +"';")

with:

db.sqlexecute("ALTER TABLE " + oldname + " RENAME TO " + newname + ";")

Note that all I’ve done is to remove the single quotes from your SQL statement.

If it doesn’t work, it would help if you share the error message.

Thanks Jason

I tried that and coming up with an error message:-
‘[Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement’

This is the newcode I am using

Dim db As New ODBCDatabase
db.DataSource = DBname
if db.connect then
  db.sqlexecute("ALTER TABLE " + oldname + " RENAME TO " + newname + ";")
end if
if db.error then
  msgbox(db.errormessage)
end if

Would it matter if the oldname and newname have spaces in the string?

Thank you

Yep. Then you’d need to enclose the table names in square brackets, so:

db.sqlexecute("ALTER TABLE [" + oldname + "] RENAME TO [" + newname + "];")

Thanks Jason, Ive tried this and unfortunately still coming up with the same error message. Is there anything else I could be missing?

Then I think you’ve got a problem :). I just did a quick search and I didn’t find a reference to the “RENAME” command being supported by Microsoft Access databases. According to this page, Access databases don’t support it. You’ll have to stick with creating a new table and then running an SQL statement to insert the data from the old table into the new one. You can do the insert in one statement.

Thanks Jason, I think I solved the problem with the link you included in your last message. I had to use two lines of code:-

SELECT * INTO newtablename FROM oldTableName
DROP TABLE oldTableName

Thanks for your help with this.