I need an example to write a script to change the Require property in access databse table

[William Gaatjes]

I am playing around with using a database and i want to write a script to change all the required properties from all the fields in the table as i want them. I want to set them as i would like them. Some need to be yes as they are required.
Others are optional and be set to required is no.
I can do this manually, but a macro or a script would be ideal to automate the process.
I have zero experience but i am a fast learner and i can always use the trial and error method and iterate to get something to function.

I found this site but i am still not clear what to do.
https://docs.microsoft.com/en-us/of...atabase-reference/field-required-property-dao

This example looks great to start from.
I have no idea to get it running in access.

Sub RequiredX()
 
 Dim dbsNorthwind As Database
 Dim tdfloop As TableDef
 
 Set dbsNorthwind = OpenDatabase("Northwind.mdb")
 
 With dbsNorthwind
 ' Show which fields are required in the Fields
 ' collections of three different TableDef objects.
 RequiredOutput .TableDefs("Categories")
 RequiredOutput .TableDefs("Customers")
 RequiredOutput .TableDefs("Employees")
 .Close
 End With
 
End Sub
 
Sub RequiredOutput(tdfTemp As TableDef)
 
 Dim fldLoop As Field
 
 ' Enumerate Fields collection of the specified TableDef
 ' and show the Required property.
 Debug.Print "Fields in " & tdfTemp.Name & ":"
 For Each fldLoop In tdfTemp.Fields
 Debug.Print , fldLoop.Name & ", Required = " & _
 fldLoop.Required
 Next fldLoop
 
End Sub

Can someone show me the way to get this running and give me an example to change the required property from yes to no and vice versa ?
Thank you in advance.

 

[Ajay]

Haven’t done this stuff in forever, but where does it fail? Is dbsNorthwind null (“”)?

 

[William Gaatjes]

I am not sure it fails, to be honest.
I have not yet figured out how to run it in access.

 

[William Gaatjes]

I figured out how to get VBA scripts to run access.
http://www.herongyang.com/VBScript/Introduction-Visual-Basic-in-Microsoft-Access.html

As a comparison, this tutorial shows you how to write Visual Basic code and run it as a macro with Microsoft Access.
Microsoft Access is a Microsoft application that can be used to store and manage data in database tables. Microsoft Access also supports a macro module that allows you to write macro code with Visual Basic (VB) language.
If you have Microsoft Access installed on your Windows system, you can follow the steps below to create a simple application in Visual Basic language within Microsoft Access.

1. Run Microsoft Access, and create a blank Access Database called vb_tutorial.mdb.
2. Click Insert > Module from the menu. The Microsoft Visual Basic window shows up.
3. Enter the following code into the empty code module:

Sub Main()
MsgBox ("Hello world! - Visual Basic in Access")
End Sub

4. Click File > Save from the menu. Enter "Hello" as the module name and save it.
5. Click Run > Run Sub/UserForm from the menu. The macro selection dialog box shows up.
6. Select "Main" macro, and click "Run". A dialog box shows up with the following message:
Hello world! - Visual Basic in Access
Congratulations. You have successfully written a Visual Basic macro in Microsoft Access!

What happened here was:

• We have added a VB macro called "Hello" to our Access database, vb_tutorial.mdb.
• We have added a VB procedure called "Main" in the VB macro. Access calls this procedure as a macro.
• The "Main" procedure calls the "MsgBox" function, which is a VB built-in function that displays Windows dialog box with the specified text message.
• We ran the "Main" procedure and got exactly what we expected.

Now the issue is that i use access 2010 because that is what i have but the site refers to access 2013.
Now i have to find a solution to that issue.

 

[Ajay]

https://www.jaffainc.com/SQLStatementsInAccess.htm#Access2007

Though, you may need to creat an admin level access workspace first.

 

[Ajay]

Duh, that’s just for queries, not vba : http://www.worldbestlearningcenter.com/index_files/VBA-Access2010-introduction.htm

 

[William Gaatjes]

Thank you. But how do i get to set or clear the required property of a field ?
It seems only supported since access 2013.

 

[Ajay]

Hopefully, somebody who’s done this in the past 10 years will pop into this thread.

 

[Cogman]

Thank you. But how do i get to set or clear the required property of a field ?
It seems only supported since access 2013.

I'd suggest not doing that via VBA and instead do it through sql queries. ORMs are OK for some things, but schema updates/changes should pretty much always be done via SQL IMO.

The thing about learning how to do this sort of stuff with SQL is that once you know it, you know how to do it not just for Access, but also sql server, oracle, my sql, postgres, etc. When you learn how to do it with VBA and access, you basically know only how to do it with VBA and Access.

It looks like VBA has a SQL interface for access, I'd read up on how to get that going and once you feel dangerous enough, go for it.

 

[mxnerd]

Maybe

Sub RequiredX()
 
 Dim dbsNorthwind As Database
 Dim tdfloop As TableDef
 
 Set dbsNorthwind = OpenDatabase("Northwind.mdb")
 
 With dbsNorthwind
 ' Show which fields are required in the Fields
 ' collections of three different TableDef objects.
 SetRequired .TableDefs("Categories")
 SetRequired .TableDefs("Customers")
 SetRequired .TableDefs("Employees")
 .Close
 End With
 
End Sub
 
Sub SetRequired(tdfTemp As TableDef)
 
 Dim fldLoop As Field
 
 ' Enumerate Fields collection of the specified TableDef
 ' and set the Required property.

 For Each fldLoop In tdfTemp.Fields
    Set fldLoop.Required = True
 Next fldLoop
 
End Sub

 

[William Gaatjes]

I'd suggest not doing that via VBA and instead do it through sql queries. ORMs are OK for some things, but schema updates/changes should pretty much always be done via SQL IMO.

The thing about learning how to do this sort of stuff with SQL is that once you know it, you know how to do it not just for Access, but also sql server, oracle, my sql, postgres, etc. When you learn how to do it with VBA and access, you basically know only how to do it with VBA and Access.

It looks like VBA has a SQL interface for access, I'd read up on how to get that going and once you feel dangerous enough, go for it.

When i have the time would love to do that.
Would really like to learn how to use SQL.
But all neuron time i have available is allocated to a project i am doing for work.

 

[William Gaatjes]

Maybe

Sub RequiredX()

Dim dbsNorthwind As Database
Dim tdfloop As TableDef

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

With dbsNorthwind
' Show which fields are required in the Fields
' collections of three different TableDef objects.
SetRequired .TableDefs("Categories")
SetRequired .TableDefs("Customers")
SetRequired .TableDefs("Employees")
.Close
End With

End Sub

Sub SetRequired(tdfTemp As TableDef)

Dim fldLoop As Field

' Enumerate Fields collection of the specified TableDef
' and set the Required property.

For Each fldLoop In tdfTemp.Fields
    Set fldLoop.Required = True
Next fldLoop

End Sub

Thank you. I will see if i can get it to work.

 

[William Gaatjes]

I modified the names a bit.
I tried instead of True below, also Yes but i get the same error.

Sub RequiredX()

Dim db As database

' open de database
Set db = OpenDatabase("Local_database.accdb")

With db
' pass on the table in the database.
    SetRequired .TableDefs("dbo_parts")
.Close
End With

End Sub

Sub SetRequired(table As TableDef)

Dim fldLoop As Field

' Enumerate Fields collection of the specified TableDef
' and set the Required property.

For Each fldLoop In table.Fields
    Set fldLoop.Required = True
Next fldLoop

End Sub

I get an compile error :
Invalid use of property.
for this line.

Set fldLoop.Required = True

And the help button gives me this text :

Procedure type mismatch

You are using one kind of Property procedure where a different kind is expected. This error has the following causes and solutions:


You are trying to write to a property that is read-only.
If the only property procedure defined for the property is a Property Get, you can't assign a value to the property. Either write an appropriate Property Let procedure, or don't attempt to write to the property.

You are trying to read a property that is write-only.
If the only property procedure defined for the property is a Property Let, you can't read the value of the property. Either write an appropriate Property Get procedure, or don't attempt to write to the property.

You are trying to set a reference but the property has only Property Get or Property Let procedures.
Either write a Property Set procedure for the property, or don't try to set a reference to it.

I will just fiddle around a bit but i have a serious lack of experience with this.

 

[mxnerd]

Maybe don't need Set keyword for the statement?

Set keyword probably only required while assigning object.

Haven't programmed MS Access for a long time.

==

Tested. You just have to remove the Set keyword in the front and it will work.

In my case I also need to change

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
to
Set dbsNorthwind = CurrentDb

though, since the database has been open.

 

[mxnerd]

OK. The complete code will be

Sub RequiredX()

    Dim db As Database
    Dim tdf As TableDef
   
    Set db = CurrentDb

    ' loop through all tables
    With db
       ' ignore system and temporary tables
       For Each tdf In db.TableDefs
           If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
               SetRequired tdf
           End If
       Next
       .Close
    End With
   
    Set tdf = Nothing
    Set db = Nothing

End Sub

Sub SetRequired(tdfTemp As TableDef)

    Dim fldLoop As Field

     ' Enumerate Fields collection of the specified TableDef
     ' and set the Required property.
     Debug.Print "Fields in " & tdfTemp.Name & ":"
     For Each fldLoop In tdfTemp.Fields
        fldLoop.Required = True
        ' open immediate window to see output
        Debug.Print , fldLoop.Name & ", Required = " & fldLoop.Required
     Next fldLoop

End Sub

 

[William Gaatjes]

Indeed it works. Thank you very very very very very very much.

Modified below :
This will set for all fields, the property Required as false.
That is perfect for me for now.
No more annoying pop up dialogs.

' Press ALT+G to open the Immediate(debug window)

Sub RequiredX()

Dim db As database

Set db = OpenDatabase("Local_database.accdb")

With db
    SetRequired .TableDefs("dbo_parts")
.Close
End With

End Sub

Sub SetRequired(table As TableDef)

Dim fldLoop As Field

' Enumerate Fields collection of the specified TableDef
' and set the Required property to False.
Debug.Print "Fields in " & table.Name & ":"
For Each fldLoop In table.Fields
    fldLoop.Required = False
    Debug.Print , fldLoop.Name & ", Required = " & fldLoop.Required
Next fldLoop

End Sub