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.
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
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).
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?
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.