How to set the Required attribute of a field to NO

When i create a new column to an Access database, I use the following line:
.Attributes = adColNullable

Is this the same as setting the 'Required' field to "NO" when viewing within Access?

If not, what do I need to do to set the 'Required' property to "NO"?  I've tried
.Required = False
but this throws an error.

Thanks
BrianBeckAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
ok, to test my theory, I created a sample function
this creates a new field called X2 in my table called Table1
I set Nullable to True which means Required=No
I then oopened my DB and it has the right setting. I then switched Nullable to False and it now turns up as Required!



    Dim adoConn As adodb.Connection
    Dim adoCat As ADOX.Catalog
    Dim adoColumn As ADOX.Column
   
    Set adoConn = New adodb.Connection
    adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EE\db1.mdb"
    adoConn.Open
   
    Set adoCat = New ADOX.Catalog
    Set adoCat.ActiveConnection = adoConn
   
    Set adoColumn = New ADOX.Column
    With adoColumn
         Set .ParentCatalog = adoCat
         .Name = "X2"
         .Type = adLongVarWChar
         .Properties("Nullable") = True
         .Properties("Jet OLEDB:Allow Zero Length") = True
    End With
    adoCat.Tables("Table1").Columns.Append adoColumn
   
    Set adoColumn = Nothing
    Set adoCat = Nothing
    adoConn.Close
    Set adoConn = Nothing
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the following script shows how to do:

Dim cat As New ADOX.Catalog

cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

cat.Tables("Table2").Columns("Field2").Attributes =  cat.Tables("Table2").Columns("Field2").Attributes AND adColNullable

'for information, to set the allow zero lenght:
'cat.Tables("Table2").Columns("Field2").Properties("Jet OLEDB:Allow Zero Length")
0
 
rockiroadsCommented:
DAO example again :)

dbs is the DAO.Database variable, one u probably already got defined (as per my last post?)


sTable = "tblFred"
sField = "Apples"

dbs.TableDefs(sTable).Fields(sField).AllowZeroLength = False
dbs.TableDefs(sTable).Fields(sField).Required = False
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
BrianBeckAuthor Commented:
Thanks angelIII, but I'm still not clear

Does it mean that adColNullable is the same as setting the Required property of an Access field to "NO"?
0
 
rockiroadsCommented:
Brian, u are on the right lines

adColNullable is basically saying whether its not a required properrty. adColFixed  is what u need to set to make it required

Allow Zero Length works with text/memo fields and indicates whether a zero length string "" (not a null be "") is a valid value or not
0
 
BrianBeckAuthor Commented:
Hi rockiroads

Thanks, as it turns out the module has the following entry:
.Columns(item1).Properties("Jet OLEDB:Allow Zero Length").Value = True

So I tried what I thought was the right syntax:
.Columns(item1).Properties("Jet OLEDB:Required").Value = False, but it threw an 'item cannot be found' error

Any thoughts?
0
 
rockiroadsCommented:
try using Nullable instead of Required, I believe that is one property available to ADOX
0
 
BrianBeckAuthor Commented:
Hi rockiroads

Thanks - such a demonstration makes it very clear indeed!!
0
 
rockiroadsCommented:
No probs :)


Ive given u a mixture of ADO/ADOX and previously DAO eh
0
All Courses

From novice to tech pro — start learning today.