How do I programmatically set a Field's Required property?

How do I programmatically set a Field's Required property to Yes if it is No and No if it is Yes?
bobbatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Heathcliff74Commented:
Dim cat As New ADOX.Catalog
Dim tblADOX As New ADOX.Table
Dim colADOX As New ADOX.Column
cat.ActiveConnection = CurrentProject.AccessConnection
Set tblADOX = cat.Tables("MyTable")
Set colADOX = tblADOX.Columns("MyField")
colADOX.Properties("Nullable") = True ' Required!
Set cat = Nothing
Set tblADOX = Nothing
Set colADOX = Nothing

Replace "MyTable" and "MyField" with the tablename and fieldname you wish to edit.
0
Heathcliff74Commented:
Forgot to tell: setting the property "nullable" to true means "not required" and setting "nullable" to false means "required".
0
bobbatAuthor Commented:
What reference do I need to install for ADOX?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bobbatAuthor Commented:
I get error: 3251, Object or provider is not capable of performing requested operation, in line:
Set tblADOX = cat.Tables("strTableName")

I call the function with:
    z = ChangeFieldProperty_Required("tblInvoice", "strInvoiceNumber", True)

Can you help me?
Function ChangeFieldProperty_Required(strTableName As String, strFieldName As String, bolIsRequired)
    Dim cat As New ADOX.Catalog
    Dim tblADOX As New ADOX.Table
    Dim colADOX As New ADOX.Column
    
    cat.ActiveConnection = CurrentProject.AccessConnection
    
    
    'Replace "MyTable" and "MyField" with the tablename and fieldname you wish to edit.
    Set tblADOX = cat.Tables("strTableName")
    Set colADOX = tblADOX.Columns("strFieldName")
    
    
    'Setting the property "nullable" to true means "not required" and setting "nullable"
    '    to false means "required".
    If bolIsRequired = True Then
        colADOX.Properties("Nullable") = True   'NOT Required!
    Else
         colADOX.Properties("Nullable") = False 'Required!
    End If
    
    Set cat = Nothing
    Set tblADOX = Nothing
    Set colADOX = Nothing
 
End Function

Open in new window

0
Heathcliff74Commented:
You have to reference "Microsoft ADO Ext". You should reference a typelib, if possible. Look for a description here: http://www.source-code.biz/snippets/vbasic/5.htm

The line which fails looks good. I think the error is caused by a wrong connection. I assumed you were using VBA within Access. You might want to replace the line which sets the ActiveConnection with this one:

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Full Path\Database.mdb"

Replace the part "C:\Full Path\Database.mdb" with a reference to your own database. I think you should be able to access the table now.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use this instead, if you'd prefer to use DAO instead. DAO is really the preferred method to work with Jet databases. To use this, just call it like this:

ToggleRequiredProperty "Your Table Name", "Your Field Name"

If you need to do this for a database other than the one you're running the code, you'd change this line:

Set dbs = Currentdb

to

Set dbs = DAO.OpenDatabase("Path to your db")
Function ToggleRequiredProperty(TableName As String, FieldName As String) As Boolean
 
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
 
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(TableName)
Set fld = tdf.Fields(FieldName)
 
fld.Required = Not fld.Required
 
Set tdf = Nothing
Set fld = Nothing
Set dbs = Nothing
 
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Heathcliff74Commented:
@LSMConsulting:

"DAO is really the preferred method to work with Jet databases."

That really depends. DAO is more native Jet. ADO is more platform independant. For this specific purpose it really doesn't matter which one to use DAO or ADO. The code you provide should work too. It really does the same as the code I provided.
0
bobbatAuthor Commented:
I'm new to ADO--this is my first time.
I get run-time error 3265: Item cannot be found in the colledction corresponding to the requested name or ordinal.

I'm using line: cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Robert Batchelor\Desktop\MakeNewBEHere\New Core BE Version 12 Nov 08.mdb"
for the connection instead of  cat.ActiveConnection = CurrentProject.AccessConnection.
I am in Access 2003/VBA
0
Heathcliff74Commented:
Can you tell me on which line the error occurs? This means that either the tablename could not be found in the collection of tables, or the FieldName cannot be found in the collection of columns. Please verify that you did not make any type-errors in the tablename and fieldname.
0
Heathcliff74Commented:
Ahh! I see the problem. Just looked at your code again. The strTableName and strFieldName should not have the double-quotes before and after. It now tries to find table "strTableName" instead of the tablename passed in the parameter. Remove the double-quotes and that should do it!
0
bobbatAuthor Commented:
Error in: Set tblADOX = cat.Tables("strTableName")

The calling command line is:
 z = ChangeFieldProperty_RequiredADO("tblInvoice", "strInvoiceNumber", False)

The function is in the attached snippet below:
Function ChangeFieldProperty_RequiredADO(strTableName As String, strFieldName As String, bolIsRequired)
    Dim cat As New ADOX.Catalog
    Dim tblADOX As New ADOX.Table
    Dim colADOX As New ADOX.Column
    
'   cat.ActiveConnection = CurrentProject.AccessConnection
 
'   Replace the part "C:\Full Path\Database.mdb" with a reference to your own database.
'   I think you should be able to access the table now.
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Robert Batchelor\Desktop\MakeNewBEHere\New Core BE Version 12 Nov 08.mdb"
    
    'Replace "MyTable" and "MyField" with the tablename and fieldname you wish to edit.
    Set tblADOX = cat.Tables("strTableName")
    Set colADOX = tblADOX.Columns("strFieldName")
   
    'Setting the property "nullable" to true means "not required" and setting "nullable"
    '    to false means "required".
    If bolIsRequired = True Then
        colADOX.Properties("Nullable") = True   'NOT Required!
    Else
         colADOX.Properties("Nullable") = False 'Required!
    End If
    
    Set cat = Nothing
    Set tblADOX = Nothing
    Set colADOX = Nothing
 
End Function

Open in new window

0
Heathcliff74Commented:
Please look at my previous comment. That should fix the problem!
0
bobbatAuthor Commented:
Error in: colADOX.Properties("Nullable") = False 'Required!

Error: Run-time error '2147217887 (80040e21)':
Multiple-ste OLE DB operation generated errors.  Check each OLE DB status value, if available.  No work was done.
unction ChangeFieldProperty_RequiredADO(strTableName As String, strFieldName As String, bolIsRequired)
    Dim cat As New ADOX.Catalog
    Dim tblADOX As New ADOX.Table
    Dim colADOX As New ADOX.Column
    
'   cat.ActiveConnection = CurrentProject.AccessConnection
 
'   Replace the part "C:\Full Path\Database.mdb" with a reference to your own database.
'   I think you should be able to access the table now.
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Robert Batchelor\Desktop\MakeNewBEHere\New Core BE Version 12 Nov 08.mdb"
    
    'Replace "MyTable" and "MyField" with the tablename and fieldname you wish to edit.
    Set tblADOX = cat.Tables(strTableName)
    Set colADOX = tblADOX.Columns(strFieldName)
    
    
    'Setting the property "nullable" to true means "not required" and setting "nullable"
    '    to false means "required".
    If bolIsRequired = True Then
        colADOX.Properties("Nullable") = True   'NOT Required!
    ElseIf bolIsRequired = False Then
         colADOX.Properties("Nullable") = False 'Required!
    End If
    
    Set cat = Nothing
    Set tblADOX = Nothing
    Set colADOX = Nothing
 
End Function

Open in new window

0
Heathcliff74Commented:
Okay. Did some more investigation. It seams the "nullable" property is read-only once the table is created. DAO provides means to create a new table for you, change the property for you on the new table and copy the contents from the old to the new table, all in the background.

However it is possible to do it with ADO, but it needs a lot more code. You need to copy all colums with all their properties and indexes yourself. Meanwhile alter the properties you need to be changed and then copy the contents. DAO seems to do this all automatic.

Forget ADO for this. It is too intervening. You should make a reference to the "Microsoft DAO object library" and use this code instead:


Function ChangeFieldProperty_RequiredADO(strTableName As String, strFieldName As String, bolIsRequired)
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim f As DAO.Field
    Set db = CurrentDb
    Set td = db.TableDefs(strTableName)
    Set f = td.Fields(strFieldName)
    fld.Required = bolIsRequired
End Function

Open in new window

0
bobbatAuthor Commented:
It works, I just change f to fld in two lines.  Final version in Snippet.


Function ChangeFieldProperty_RequiredADO(strTableName As String, strFieldName As String, bolIsRequired)
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fld As DAO.Field
    Set db = CurrentDb
    Set td = db.TableDefs(strTableName)
    Set fld = td.Fields(strFieldName)
    fld.Required = bolIsRequired
End Function

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DAO is tightly coupled with the Jet database engine and has long been a faster and better method for working with Jet databases in this manner. Matter of fact, there are things you cannot do with ADO/ADOX in regards to Jet - for example, you cannot accurately manipulate security with ADO/ADOX, and you cannot get to some of the native properties with ADO/ADOX (i.e. the Description property of a table, if that's at all important to anyone). The new Access 2007 DAO also is the only means you can use to work with the new multi-valued fields. But you are right - in most cases, you can use ADO/ADOX the same as you would DAO.

That said, I use ADO almost exclusively for data access, but when it comes time to work with the internal structure of a Jet database I always switch to DAO.

Bobat: Is there some reason why you chose the DAO solution by HeathClif (#22972371) as an Assisted solution, and did not choose the almost identical DAO code that I provided (#22970351) as well? Just curious ...

0
bobbatAuthor Commented:
What you see here is not what I did and I don't know what happened but I will contact the admin person to set the record straight.  Here is what I really did last night:  I assigned 400 points to #22970351 (Thank you for your prompt response) because that is the code I'm using and 100 to #22972371 (too clever for me--not my style--and it had two f's instead of fld's).  I did NOT award any points to 22968544 because it did not/could not work.  I know what I did and this is not it.  I will attempt to correct the situation.
0
Heathcliff74Commented:
Couldn't agree more that LSMConsulting deserves points here too. He proposed the DAO alternative. Ciao!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.