How to Rename Access Table:

[Thomas H. Lanier]

Does anyone know how to rename an Access table in VB.Net with OleDb?

For instance, this does NOT work:

cn = New OleDbConnection(ConnectionString)
cn.Open()
sql = "ALTER TABLE Test1 RENAME Test2"
cmd = New OleDbCommand(sql, cn)
cmd.ExecuteNonQuery()

Thanks,

Tommy

 

[Thomas H. Lanier]

For anyone else who has this problem, this seems to work; however, I can't
believe there's not an easier, more direct way to do this.

Add references to:

Microsoft ActiveX Data Objects 2.8 Library
Microsoft ADO Ext. 2.8 for DDL and Security

'*******************************************************************************
' Rename Table
'*******************************************************************************
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


Hope this helps someone else.

Tommy

 

[Kevin Yu [MSFT]]

Hi Tommy,

As far as I know, we cannot use RENAME on ALTER TABLE statement. For Access
database, the only way to rename a table is to copy the table or use ADOX.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."