Microsoft KB Archive/200407

= ACC: Setting DAO Required Property Against SQL Server Fails Silently =

Article ID: 200407

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q200407





Advanced: Requires expert coding, interoperability, and multiuser skills.



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 that this is a problem in the Microsoft products that are listed at the beginning of this article.



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= ;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= ;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 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 </li> 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.</li></ol>

<div class="references_section">