Modify field properties using ADO

[webcats]

Hi,

I'm trying to modify a table's field properties using ADO. I've seen examples using DAO, but I need to use ADO.

I've tried this:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set rst = New ADODB.Recordset
rst.Open "SouthBay", CurrentProject.Connection

Set fld = rst.Fields("DSCT_VALUE")

With fld
.Properties = adCurrency
End With

rst.Close
Set rst = Nothing
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

But I'm getting an "invalid use of property" for the .properties = adCurrency line.

Can anyone help? Thanks.

 

[cmmrfrds]

Use ADOX to work with the catalog. There should be enough code in this function to get you started.

Function catalogInfo()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As ADOX.Column
Set cg.ActiveConnection = CurrentProject.Connection

''tb.Name = "Test"
''tb.Columns.Append "col1", adInteger
''tb.Columns.Append "col2", adVarWChar, 50
''tb.Columns.Append "col2aa", adInteger
''Debug.Print "table = "; cg.Tables("Test").Name
''cg.Tables.Append tb
''Exit Function

'-rename a column
Set tb = cg("test")
Debug.Print "tb = "; tb.Name

Set cl = tb("col2aa")
Debug.Print cl
cl.Properties("Jet OLEDB:Allow Zero Length") = True
cl.Name = "col2bb"
Exit Function
End Function

 

[webcats]

Thanks cmmrfrds,

I've tried it, but I am getting an error:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

code:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim col As ADOX.Column
Dim prop As Property

Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("SouthBay")
Set col = tbl("DSCT_VALUE")

tbl.Properties("Jet OLEDB:Format") = adCurrency

cat.ActiveConnection.Close
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

What am I doing wrong? Thanks.

 

[webcats]

Oops. I noticed that I was referencing the table instead of the column (field).

So I changed this line:

tbl.Properties("Jet OLEDB:Format") = adCurrency

to this:

col.Properties("Jet OLEDB:Format") = adCurrency

But I'm still getting the same error...

 

[cmmrfrds]

Debug.Print col.Type
I checked and apparently the 'type' is one attribute that ADOX does not support modifying.

Should have been able to do.
col.Type = adCurrency
It does support rename of the column.
col.Name = "newname"

I suppose the work around is to add a new column of the type you want, then copy the data from the old column and then drop the old column and then rename the new column.

Maybe somebody else can weigh in on this. Otherwise, the SQL ALTER Table statement should work in any case.