asked on

How to Allow null fields in ADO

I keep getting "the field cannot contain a null value because the required property for this field is set to true..." when I try and open a newly created database. Any way to allow for null fields? or at least empty ones?

Public Sub NewDatabase()

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

Userfile = dbPath & "TEMP" & FileNo & ".MDB"

cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Userfile & ";" & _
"Jet OLEDB:Engine Type=4;"

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Userfile

With tbl
.Name = "OMWII"
.Columns.Append "StockName", adVarWChar, 40
.Columns.Append "StockSymbol", adVarWChar, 6
.Columns.Append "StockPrice", adSingle

end with

cat.Tables.Append tbl

Set tbl = Nothing
Set cat = Nothing

Screen.MousePointer = vbNormal

Connect2Database

End Sub

Public Sub Connect2Database()

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& Userfile & ";Persist Security Info=False"

rst.Open "SELECT * FROM OMWII", cnn, adOpenStatic, adLockOptimistic
rst.AddNew

'For i = 0 To rst.Fields.Count - 1
' rst.Fields(i).Value = 0
'Next i

'For Each fld In rst.Fields
' Debug.Print fld.Name & " = " & fld.Value
'Next

rst.Update
rst.MoveNext
If rst.EOF Then rst.MoveLast 'if moved to EOF, go to last record so don't get error.

rst.MoveFirst

Screen.MousePointer = vbNormal

End Sub

mmips

The following code uses the properties of the column object to create provider specific column types. Ihave tried used this to set the column to allow nulls.

see:ParentCatalog Property Example (Visual Basic)

Sub CreateAutoIncrColumn()

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:\Program Files\" & _
"Microsoft Office\Office\Samples\Northwind.mdb;"
Set cat.ActiveConnection = cnn

With tbl
.Name = "MyContacts"
Set .ParentCatalog = cat
' Create fields and append them to the new Table object.
.Columns.Append "ContactId", adInteger

' Make the ContactId column and auto incrementing column
.Columns("ContactId").Properties
("AutoIncrement") = True

.Columns.Append "CustomerID", adVarWChar
.Columns.Append "FirstName", adVarWChar
.Columns.Append "LastName", adVarWChar
.Columns.Append "Phone", adVarWChar, 20
.Columns.Append "Notes", adLongVarWChar
End With

cat.Tables.Append tbl

Set cat = Nothing

End Sub

sunilkk

go to table column and set default as 0 (zero).

deepakg

Hi,
When you are creating the Table (OMWII) dynamically, set the 'Required' property of the field (that should allow nulls) to False like this:

..Columns("FieldName").Properties("Required")=False

This will solve your problem....

Deepak

deepakg

Just remembered,
Also set the 'Allow Zero Length' property to False, if you want to allow Nulls (Just a caution)

Deepak.

ASKER CERTIFIED SOLUTION

Jeremy_D

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jfyfe

ASKER

Thanks for the suggestions, the one I found easiest to implement without getting errors was Jeremy D's.