RENAME and set new length of field

[sal21]

in a access table have this field,PROVICIA txt property and length 50, in table SICILIA

i need to rename the filed PROVINCIA in PR txt property and length 2 and indexed duplicates YES

how to with a SQL on ADO and Vb6?

Note:
my connetion is CN
the original filed just is indexed duplicates YES

[HansV]

You can't rename a field in ADODB; you need ADOX too.
Set a reference to the Microsoft ADO Ext. ... for DDL and Security" library. The ... is a number that may vary (I have 2.8 and 6.0 on my computer).
Code:

Code: Select all

Sub ChangeField()
    Dim CAT As ADOX.Catalog
    Dim TBL As ADOX.Table
    Dim COL As ADOX.Column
    Set CAT = New ADOX.Catalog
    CAT.ActiveConnection = CN
    Set TBL = CAT.Tables("SICILIA")
    Set COL = TBL.Columns("PROVINCIA")
    COL.Name = "PR"
    CN.Execute "ALTER TABLE SICILIA ALTER COLUMN PR CHAR(2)"
End Sub

[sal21]

You can't rename a field in ADODB; you need ADOX too.
Set a reference to the Microsoft ADO Ext. ... for DDL and Security" library. The ... is a number that may vary (I have 2.8 and 6.0 on my computer).
Code:

Code: Select all

Sub ChangeField()
    Dim CAT As ADOX.Catalog
    Dim TBL As ADOX.Table
    Dim COL As ADOX.Column
    Set CAT = New ADOX.Catalog
    CAT.ActiveConnection = CN
    Set TBL = CAT.Tables("SICILIA")
    Set COL = TBL.Columns("PROVINCIA")
    COL.Name = "PR"
    CN.Execute "ALTER TABLE SICILIA ALTER COLUMN PR CHAR(2)"
End Sub

GREAT!

OPS...

but I need to check before if PROVINCIA exists....
how to?

[HansV]

You can use an error handler:

Code: Select all

Sub ChangeField()
    Dim CAT As ADOX.Catalog
    Dim TBL As ADOX.Table
    Dim COL As ADOX.Column
    Set CAT = New ADOX.Catalog
    CAT.ActiveConnection = CN
    Set TBL = CAT.Tables("SICILIA")
    On Error GoTo ExitHere
    Set COL = TBL.Columns("PROVINCIA")
    COL.Name = "PR"
    CN.Execute "ALTER TABLE SICILIA ALTER COLUMN PR CHAR(2)"
ExitHere:
End Sub