Rename A Table

[Hackster]

I need to rename a table in an Access database (this is an import database that gets shipped to us once a week, and for reasons too lenghty to go into, I have to rename one of the tables before I can process that data in it.) Here is what I have:

Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\DBName.mdb;" & _
        "Persist Security Info=False"

Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
sSQL = "ALTER TABLE table_name RENAME 'table_name_new'"
ADOCn.Execute sSQL

I'm getting a syntax error, the description for which is anything but helpful. How do you programmatically rename a table in a DB?

 

[AndyLee100]

What is the error message. I know you say it isnt helpful but it may help????

 

[bjd4jc]

I am not saying that you can't do it somehow like that but I've never seen it.

You could do a

SELECT * INTO table_name_new FROM table_name

and then

DROP TABLE table_name

 

[bjd4jc]

Here is also a snippet of code that will do the rename job:

Private Sub Form_Load()
    Dim ADOcn As Object
    Set ADOcn = CreateObject("ADODB.Connection")
    ADOcn.provider = "Microsoft.Jet.OLEDB.4.0"
    ADOcn.open "C:\db.mdb"
    
    RenameTable ADOcn, "Table2", "Table2_New"
End Sub

Public Sub RenameTable(ADOcn As Object, OldName As String, NewName As String)
    With CreateObject("ADOX.Catalog")
        .activeConnection = ADOcn
        .tables(OldName).Name = NewName
    End With
End Sub

 

[Hackster]

Thanks bjd4jc. Works great!