How can I rename an MS Access Table with an SQL query?

selet * into newtable from oldtable
drop oldtable

this is only way for access,i think.

n1011
December 2010

Without having to use VB you can use:

SELECT * INTO {NEWTABLE} FROM {OLDTABLE};

Then:

DROP TABLE {OLDTABLE};

You will lose any indexes but it is quite easy to rebuild them using SQL too.

Alex Jephson
October 2006

There is no way to Rename using SQL (that I know of) without writing code and the only way that I know how that is somewhat what you're looking for is to create a new table using Access' SQL and then use the IMPORT feature:

IE: The syntax of creating a table is

CREATE TABLE TAbleName;

The CREATE and TABLE keywords are required to indicate that you want to create a table.

Then to import a table, you can first display the New Table dialog box, click Import Table and click OK, or you can double-click Import Table. Alternatively, on the main menu of Microsoft Access, you can click File -> Get External Data -> Import... Either of these actions would call the Import dialog box from where you can select the application or the database that is holding the table you want to import. After selecting the application or the database, the Import Objects dialog box would come up.

From there, you can select the category and the objects you want to import, then click OK.

Otherwise you have to use ADOX (ADO eXtension) and implement the code below:

Sub RenameTable(ByVal oldName As String, ByVal newName As String)
Dim cn As New ADODB.Connection
Dim catalog As New ADOX.Catalog
Dim i As Integer


cn.ConnectionString = mConnectionString
cn.Open()


catalog.ActiveConnection = cn


For i = 0 To catalog.Tables.Count() - 1
If catalog.Tables(i).Name = oldName Then
catalog.Tables(i).Name = newName
Exit For
End If
Next


cn.Close()


cn = Nothing
catalog = Nothing
End Sub

You can also refer to: http://www.4guysfromrolla.com/webtech/tips/t030802-1.shtml

stevedude
October 2005