ACC: Setting DAO Required Property Against SQL Server Fails Silently

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new blank database named TestDatabase.
2. Create a module, and type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

3. Type the following procedure:

    
   Sub CreateSQLServerTable()
   
       Dim db As Database
       Dim td As TableDef
       Dim f As Field
   
       'Open connection to server, assuming the server
       'is running on the same machine that we run the
       'code on:
   
       Set db = OpenDatabase("", False, False, _
       "ODBC;DSN=LocalServer;UID=<username>;PWD=<strong password>;DATABASE=Pubs;")
   
       'Create a table and its field, setting the properties
       'of the field
   
       Set td = db.CreateTableDef("tblTest")
       Set f = td.CreateField("F1", dbText, 50)
       f.AllowZeroLength = False
       f.Required = True
       td.Fields.Append f
       db.TableDefs.Append td
   
       MsgBox "Table Added. The required property was set to: " & _
       vbCrLf & f.Required & vbCrLf & "Reading Table..."
   
       'Clean up
       Set f = Nothing
       Set td = Nothing
       db.Close
       Set db = Nothing
   
       'Reopen the connection to SQL Server
       Set db = OpenDatabase("", False, False, _
       "ODBC;DSN=LocalServer;UID=<username>;PWD=<strong password>;DATABASE=Pubs;")
   
       'Examine the F1 field
   
       Set td = db.TableDefs("tblTest")
       Set f = td.Fields("F1")
   
       MsgBox "The required property for column F1 is set to: " & _
       f.Required
   
   End Sub
   						

   Note You must change the values for the UID (user name) and PWD (password) parameters in the previous example to successfully connect to SQL Server. If necessary, ask your database administrator for the user name and password of an account that has permissions to create tables.
4. In the Debug window, type the following and press ENTER:

   Call CreateSQLServerTable
   					

5. Note that when you run the procedure, the Required property for the F1 field is initially set to True when the table is created. However, when the procedure re-examines the F1 field, the original setting for the Required property has been lost, and returns False.