vba rename table column (1 Viewer)

[mikeo1313]

Private Sub Command6_Click()
   'Create a Catalog object
 
   Dim con As New ADODB.Connection
   con.Open CurrentProject.AccessConnection
 
   Dim Cat As New ADOX.Catalog
   Cat.ActiveConnection = con
 
 Dim oldn As String
 Dim newn As String
 Dim tbln As String
 
tbln = "test"
oldn = "dog101"
newn = "dog"
   'Create a table object
 
   Dim Tbl As ADOX.Table
   Set Tbl = New ADOX.Table
 
   Set Tbl = Cat.Tables(tbln)
   Tbl.Columns(oldn).Name = newn
 
   Set Cat = Nothing
   Set Tbl = Nothing
 
End Sub

I get run-time error 3251

object or provider is not capable of performing requested operation
on this line

Tbl.Columns(oldn).Name = newn

using access 2007 w accdb file type

anyone got an idea of what can fix this?

 

[mikeo1313]

this ended up doing it


Dim oldn As String
Dim newn As String
Dim tbln As String
Dim tbl As ADOX.Table
Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

tbln = "test"
oldn = "dog101"
newn = "dog"
cat.Tables(tbln).Columns(oldn).Name = newn

Set cat = Nothing
Set tbl = Nothing