how can I rename a column in code?

[Guest]

how can I rename a column in vb6 code using either ADO or a SQL statement?
the database is Access 2000.

 

[Douglas J. Steele]

I don't believe it's possible using ADO or SQL: the ALTER COLUMN DDL
statement doesn't allow for column renames (although you can alter other
properties of a column using DDL)

You can do it using DAO or ADOX though.

 

[Guest]

Don't suppose you could provide an example?

I don't believe it's possible using ADO or SQL: the ALTER COLUMN DDL
statement doesn't allow for column renames (although you can alter other
properties of a column using DDL)

You can do it using DAO or ADOX though.

 

[Douglas J. Steele]

DAO:

Dim dbCurr As DAO.Database

Set dbCurr = OpenDatabase("....")
dbCurr.TableDefs("MyTable").Fields("MyOldFieldName").Name =
"MyNewFieldName"

ADOX:

I think it's

Dim cnnCurr As ADOX.Connection
Dim catCurr As ADOX.Catalog

Set cnnCurr As New ADOX.Connection
cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='...';"
Set catCurr.ActiveConnection = cnnCurr
catCurr.Tables("MyTable").Columns("MyOldFieldName").Name =
"MyNewFieldName"

 

[Guest]

Many Thanks Doug, will give it a try.

DAO:

Dim dbCurr As DAO.Database

Set dbCurr = OpenDatabase("....")
dbCurr.TableDefs("MyTable").Fields("MyOldFieldName").Name =
"MyNewFieldName"

ADOX:

I think it's

Dim cnnCurr As ADOX.Connection
Dim catCurr As ADOX.Catalog

Set cnnCurr As New ADOX.Connection
cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='...';"
Set catCurr.ActiveConnection = cnnCurr
catCurr.Tables("MyTable").Columns("MyOldFieldName").Name =
"MyNewFieldName"

 

[peregenem]

ADOX: I think it's (snipped)

Not quite. Try this:

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog

With cat
.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=filenamehere;"
.Tables("MyTable").Columns("MyOldColumnName").Name _
= "MyNewColumnName"
End With