ADOX modify column Nullable

[Mark C]

Hi

I have written an access 2000 DB which uses ADOX to open and add fields
to an existing database. I Can add fields no problem, I can modify
almost all the properties of the fields, EXCEPT the nullable property.
The code below shows the different methods I've tried. I generally get
an error number -2147217887 with description

"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."

I can change any of the column properties, name, description, zero
length, etc however not nullable. I can't change the new field, or
established fields, from this or any other table. I can't change from
true to false, or false to true without getting this error.

I'm at my wits end, can anyone help me please

Regards
Mark

===========================================================

Public Const cstrDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine
Type=5;Jet OLEDBatabase Password=xxxxxxx;Data Source=c:\file1.mdb"

<snip>
Set dbCat1 = initialize(cstrdsn)

WriteEventLog 0, "ADOX: Open", "updateTable"

With dbCat1.Tables("jrmCustomer").Columns
'For Each objTmp In .Item("ctillcustomerid7").Properties
' Debug.Print objTmp.Name; ": "; objTmp.Value
'Next
.Append "cTillCustomerID", adVarWChar, 10
.Item("cTillCustomerID").Properties("Jet OLEDB:Allow Zero Length") =
True
.Item("cTillCustomerID").Properties("Default") = Chr(34) & " " & Chr
(34)
'.Item("cTillCustomerID").Properties("NullAble") = True
GoTo end_err1

Set oColumn = New ADOX.Column
oColumn.Name = "cSpareN1"
oColumn.Type = adInteger
oColumn.Properties("Nullable") = True
.Append oColumn
'''.Append "cSpareN1", adInteger
'''.Item("cSpareN1").Properties("Nullable") = True
</snip>

 

[Ron Weiner]

I get the same results with this (very straight forward) code

Private Sub SetNullable(strTablename As String, _
strColumnName As String, blnTF As Boolean)
Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column

cat.ActiveConnection = CurrentProject.Connection
Set col = cat.Tables(strTablename).Columns(strColumnName)
Debug.Print "Before Nullable Prop "; col.Properties("Nullable")
col.Properties("Nullable") = blnTF
Set cat = Nothing
Set col = Nothing
End Sub

The property appears to be read-only as I can see the current value of the
property before I attempt to set it. I did not see anything in the
documentation that I have that even aludes to this property to being read
only. I suspect that this has gotta be some kind ADO-Jet provider bug. that
might be dependent on the MDAC version you are using.

You can set this property using DAO with the following one liner.

CurrentDb.TableDefs("YourTable").Fields("YourField").Required = False
Which would be the equavilent of col.Properties("Nullable") = True

and

CurrentDb.TableDefs("YourTable").Fields("YourField").Required = True
Which would be the equavilent of col.Properties("Nullable") = False

If I were you I would look to redo the whole she-bang using DAO. Or at
least give up on this one property an use DAO to set it. Good luck with
your project.

 

[Mark C]

I get the same results with this (very straight forward) code

The property appears to be read-only as I can see the current value of
the property before I attempt to set it. I did not see anything in
the documentation that I have that even aludes to this property to
being read only. I suspect that this has gotta be some kind ADO-Jet
provider bug. that might be dependent on the MDAC version you are
using.

If I were you I would look to redo the whole she-bang using DAO. Or
at least give up on this one property an use DAO to set it. Good luck
with your project.

In the end I gave up on ADO And went back to DAO and it worked flawlessly!
Thanks for the advice. So much for ADO(X) being the way of the future! I
had to use DAO to remove relationships too!!
Mark

 

[sfinney]

For some reason you don't use .Properties to set this, try this:

With myColumn
.Name = ColumnName
.Type = adVarWChar
.DefinedSize = 15
.Attributes = adColNullable
End With

I hope I am not too late to help!
Sa