How to Rename Access Table:

microsoft.public.dotnet.framework.adonet

13 Apr 2005 10:24 PM

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

13 Apr 2005 11:40 PM

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

Are all your drivers up to date? click for free checkup

14 Apr 2005 2:58 AM

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."