?
Solved

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

Posted on 2008-11-15
19
Medium Priority
?
592 Views
Last Modified: 2013-11-28
How do I programmatically set a Field's Required property to Yes if it is No and No if it is Yes?
0
Comment
Question by:bobbat
  • 9
  • 7
  • 2
18 Comments
 
LVL 3

Expert Comment

by:Heathcliff74
ID: 22968544
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
 
LVL 3

Expert Comment

by:Heathcliff74
ID: 22968709
Forgot to tell: setting the property "nullable" to true means "not required" and setting "nullable" to false means "required".
0
 

Author Comment

by:bobbat
ID: 22969145
What reference do I need to install for ADOX?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:bobbat
ID: 22969171
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
 
LVL 3

Expert Comment

by:Heathcliff74
ID: 22970336
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1600 total points
ID: 22970351
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
 
LVL 3

Expert Comment

by:Heathcliff74
ID: 22972121
@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
 

Author Comment

by:bobbat
ID: 22972187
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
 
LVL 3

Expert Comment

by:Heathcliff74
ID: 22972210
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
 
LVL 3

Expert Comment

by:Heathcliff74
ID: 22972228
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
 

Author Comment

by:bobbat
ID: 22972233
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
 
LVL 3

Expert Comment

by:Heathcliff74
ID: 22972240
Please look at my previous comment. That should fix the problem!
0
 

Author Comment

by:bobbat
ID: 22972270
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
 
LVL 3

Assisted Solution

by:Heathcliff74
Heathcliff74 earned 400 total points
ID: 22972371
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
 

Author Comment

by:bobbat
ID: 22972635
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
 
LVL 85
ID: 22974505
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
 

Author Comment

by:bobbat
ID: 22976005
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
 
LVL 3

Expert Comment

by:Heathcliff74
ID: 22976207
Couldn't agree more that LSMConsulting deserves points here too. He proposed the DAO alternative. Ciao!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

839 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