Required Property of the field in a table is set to TRUE... 1

[ZuperDuper]

When I try yo fill the Access table that I create, I get an error 'Required Property of the field is set to TRUE' if I do not set the particular field with any value. Apparently it cannot be NULL. This is how I create the table:


sCreateString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sResults
newdb1.Create(sCreateString)
cn.Open(sCreateString)
newdb1.ActiveConnection = cn
With newtd1
.Name = "RESULTS"
.Columns.Append("NUMBER")
.Columns.Append("DBF")
.Columns.Append("FIELD") ', ADOX.DataTypeEnum.adChar, 30)
.Columns.Append("ITEM") ', ADOX.DataTypeEnum.adChar, 50)
.Columns.Append("CODE") ', ADOX.DataTypeEnum.adChar, 10)
.Columns.Append("COUNT") ', ADOX.DataTypeEnum.adBigInt)
.Columns.Append("TOTAL") ', ADOX.DataTypeEnum.adBigInt)
.Columns.Append("OUTHOUSE") ', ADOX.DataTypeEnum.adChar, 4)

End With

newdb1.tables.append(newtd1)
newrs1.Open("select * from RESULTS", cn, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic)


then later if I .addnew() and then .update() , for any field that is not assigned a value, I get that error. What should I do to set the 'required property' to FALSE?




You should never underestimate the predictability of stupidity.

 

[Becks25Not]

After your filed length is option. You need to set the options for any field that you are allowing to be Null to adMayBeNull or adIsNullable. In mine, if you just put the comma in after the length, a list comes up. If you want more than one option for a column, just do the + (adMayBeNull + adIsNullable)

 

[ZuperDuper]

I tried that, but the only options I get are the field data types, and the length. Just like in the above. Nothing comes up after the length option. The only object that is allowed in this option is ADOX.DataTypeEnum. By the way, if I do use type and length options, I do get an error:

ERROR - System.Runtime.IteropServices.COMException (0x80040E3D): Type is invalid. at ADOX.Tables.Append(Object Item);

So I just skipped the type declaration, since it really doesnt matter to me anyways. It all should be text.
What might be you doing differently to make this work?


You should never underestimate the predictability of stupidity.

 

[Becks25Not]

I am using just straight ADO ... here's my code:

 Public grstMappedPresortFile As ADODB.Recordset = New ADODB.Recordset()
....
        If grstMappedPresortFile.State = ADODB.ObjectStateEnum.adStateOpen Then
            grstMappedPresortFile.Close()
            grstMappedPresortFile = Nothing
        End If

        With grstMappedPresortFile
            .Fields.Append("StaticFieldName", ADODB.DataTypeEnum.adVarChar, 50)
            .Fields.Append("DefaultFieldName", ADODB.DataTypeEnum.adVarChar, 50)
            .Fields.Append("Req1stClass", ADODB.DataTypeEnum.adBoolean, , ADODB.FieldAttributeEnum.adFldIsNullable)
            .Fields.Append("Req3rdClass", ADODB.DataTypeEnum.adBoolean, , ADODB.FieldAttributeEnum.adFldIsNullable)
            .Fields.Append("ReqGrayHair", ADODB.DataTypeEnum.adBoolean, , ADODB.FieldAttributeEnum.adFldIsNullable)
            .Fields.Append("ReqSCFBMC", ADODB.DataTypeEnum.adBoolean, , ADODB.FieldAttributeEnum.adFldIsNullable)
            .Fields.Append("FMTFieldName", ADODB.DataTypeEnum.adVarChar, 50, ADODB.FieldAttributeEnum.adFldIsNullable)
            .Fields.Append("FMTFieldLength", ADODB.DataTypeEnum.adInteger, , ADODB.FieldAttributeEnum.adFldMayBeNull)
            .Fields.Append("FMTFieldStartPos", ADODB.DataTypeEnum.adInteger, , ADODB.FieldAttributeEnum.adFldIsNullable)
            .Open()
        End With
....

 

[Becks25Not]

Scratch that ... check this out:

Column Object
Represents a column from a table, index, or key.

Remarks
The following code creates a new Column:

Dim obj As New Column
With the properties and collections of a Column object, you can:

Identify the column with the Name property.
Specify the data type of the column with the Type property.
Determine if the column is fixed-length, or if it can contain null values with the Attributes property.
Specify the maximum size of the column with the DefinedSize property.
For numeric data values, specify the scale with the NumericScale property.
For numeric data value, specify the maximum precision with the Precision property.
Specify the Catalog that owns the column with the ParentCatalog property.
For key columns, specify the name of the related column in the related table with the RelatedColumn property.
For index columns, specify whether the sort order is ascending or descending with the SortOrder property.
Access provider-specific properties with the Properties collection.

----

Sub AttributesX()

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim colTemp As New ADOX.Column
    Dim rstEmployees As New Recordset
    Dim strMessage As String
    Dim strInput As String
    Dim tblEmp As ADOX.Table
    
    ' Connect the catalog.
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=c:\" & _
        "Program Files\Microsoft Office\Office\Samples\Northwind.mdb;"
    Set cat.ActiveConnection = cnn

    Set tblEmp = cat.Tables("Employees")
    
    ' Create a new Field object and append it to the Fields
    ' collection of the Employees table.
    colTemp.Name = "FaxPhone"
    colTemp.Type = adVarWChar
    colTemp.DefinedSize = 24
[b]
    colTemp.Attributes = adColNullable
[/b]
    cat.Tables("Employees").Columns.Append colTemp
    
    ' Open the Employees table for updating as a Recordset
    rstEmployees.Open "Employees", cnn, adOpenKeyset, adLockOptimistic
    
    With rstEmployees
        ' Get user input.
        strMessage = "Enter fax number for " & _
            !FirstName & " " & !LastName & "." & vbCr & _
            "[? - unknown, X - has no fax]"
        strInput = UCase(InputBox(strMessage))
        If strInput <> "" Then
            Select Case strInput
                Case "?"
                    !FaxPhone = Null
                Case "X"
                    !FaxPhone = ""
                Case Else
                    !FaxPhone = strInput
            End Select
            .Update
            
            ' Print report.
            Debug.Print "Name - Fax number"
            Debug.Print !FirstName & " " & !LastName & " - ";

            If IsNull(!FaxPhone) Then
                Debug.Print "[Unknown]"
            Else
                If !FaxPhone = "" Then
                    Debug.Print "[Has no fax]"
                Else
                    Debug.Print !FaxPhone
                End If
            End If

        End If

        .Close
    End With

    ' Delete new field because this is a demonstration.
    tblEmp.Columns.Delete colTemp.Name
    cnn.Close
    
End Sub

 

[ZuperDuper]

This looks like what I need :-D
I think this will work like a charm. Thanks!

You should never underestimate the predictability of stupidity.