ACC: Setting DAO Required Property Against SQL Server Fails Silently (200407)

RESOLUTION

Instead of using DAO, execute Data Definition Language (DDL) statements from a SQL pass-through query to create a table with columns that do not allow Null values.

Note the following sample DDL statement, which creates a table named tblTest with one field named F1. The F1 field does not accept Null values:

   CREATE TABLE "tblTest" ("F1" varchar(50) NOT NULL)

MORE INFORMATION

Steps to Reproduce Behavior

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

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.

In the Debug window, type the following and press ENTER:
```
Call CreateSQLServerTable
					
```
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.

REFERENCES

For more information about creating pass-through queries in Microsoft Access 97, type the following line in the Microsoft Office Assistant: "Send commands to an SQL database using a pass-through query."

For more information about creating pass-through queries in Microsoft Access 95, type the following line the Microsoft Access 95 Answer Wizard: "Send commands to an SQL database using a pass-through query."

For more information about creating pass-through queries in Microsoft Access 2.0, Click Help, and then Search. Click the Index tab, and type "pass-through query."