?
Solved

How to set the Required attribute of a field to NO

Posted on 2006-10-31
9
Medium Priority
?
533 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:BrianBeck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17840785
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17840819
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
 

Author Comment

by:BrianBeck
ID: 17840821
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 17840864
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
 

Author Comment

by:BrianBeck
ID: 17840869
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17840915
try using Nullable instead of Required, I believe that is one property available to ADOX
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1200 total points
ID: 17840940
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
 

Author Comment

by:BrianBeck
ID: 17840983
Hi rockiroads

Thanks - such a demonstration makes it very clear indeed!!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17840991
No probs :)


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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question