There's a problem to Use ALTER Query to Rename the Table

[AlexChoi]

Hi.

I was trying to rename my table by query. 

So I found 'ALTER TABLE old_table_name RENAME TO new_table_name;' query on the internet.

But it cause error.

I try other functions like

ALTER TABLE name DROP [COLUMN] column_name 

ALTER TABLE name ADD COLUMNS

and it work fine.

I don't know why I can't change my Table by 'ALTER' statement.

Is there anybody know the reason?

[Artem.SPb]

Try sp_rename

[wiebe@CARYA]

It would be useful to know the error...

What database are you using? Apparently MySQL uses a different syntax.

[AlexChoi]

I’m working with Access. 

[Yamaeda]

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/alter-tab...

"

Note

The Microsoft Access database engine does not support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Microsoft Access databases. Use the DAO Create methods instead."

/Y

[Yamaeda]

You can Select everything and drop it into a new table, then drop the old, that'd effectively be a rename.

SELECT *
INTO [acecodetable].['PSCLineReason']
FROM [acecodetable].['15_PSCLineReason'];

DROP [acecodetable].['15_PSCLineReason']

/Y

[wiebe@CARYA]

Apparently Access doesn't support a single query to do that:

https://bytes.com/topic/sql-server/answers/704018-rename-ms-access-table-using-sql-queryhttps://byte...

[AlexChoi]

So there No way to Rename the Table by ALTER Query.

Thanks guys

---