Alter Table 1

brews · Sep 26, 2010

Want to rename a column in a table. Tried using this:

Code:

    sChkField = "Birthdate"
    If FieldExists(rs, sChkField) Then
        sAlter = "ALTER TABLE Members RENAME COLUMN BirthDate TO DOB"
        cn.Execute sAlter
    End If

Private Function FieldExists(r As ADODB.Recordset, sFieldName As String) As Boolean
    Dim fld As Field
    sSql = "SELECT * FROM Members"
    rs.Open sSql, cn

    For Each fld In r.Fields
        If fld.Name = sFieldName Then
            FieldExists = True
            Exit For
        End If
    Next fld
    r.Close
    Set fld = Nothing
End Function

But it does not accomplish that task. Any ideas?

Thanks.

Golom · Sep 27, 2010

Try something like

Code:

Dim fd    As ADOX.Column
Dim cat   As ADOX.Catalog

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentConnection
Set fd = cat.tables.Item("members").Columns("BirthDate")

fd.Name = "DOB"

Set fd = Nothing
Set cat = Nothing

brews · Oct 1, 2010

Thanks for the reply. Which Component/Reference needs to be added?

brews · Oct 1, 2010

Works, thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Alter Table 1

brews

Technical User

Golom

Programmer

brews

Technical User

brews

Technical User

Similar threads

Part and Inventory Search

Sponsor