ACC: Setting DAO Required Property Against SQL Server Fails Silently

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

When you use Data Access Objects (DAO) to set the Required property of a field in a SQL Server table, the attempt fails silently.

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) 

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

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=sa;PWD=;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=sa;PWD=;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 may need to change the values for the UID (user name) and PWD (password) parameters in the example above to successfully connect to SQL Server. If necessary, ask your database administrator for a user name and password with 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.

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."

Additional query words: pra

Keywords : kbdta
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbbug