Solved

How to set the Required attribute of a field to NO

Posted on 2006-10-31
9
499 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
  • 5
  • 3
9 Comments
 
LVL 142

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 300 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

773 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