Microsoft KB Archive/135379

= ACC: New SQL Records Appear Deleted Until Recordset Reopened =

Article ID: 135379

Article Last Modified on 1/19/2007

-

APPLIES TO


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

-



This article was previously published under Q135379





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

When you add a record to an SQL table by using Visual Basic for Applications, if the table's unique index field has a default value, and you do not assign a value to that field, the new record appears deleted until you reopen the SQL table. If you try to obtain a value from the new record, you receive the following error message:

Run-time error '3167'

Record is deleted.



RESOLUTION
When you open the SQL table by using Visual Basic code, include the dbSeeChanges option, as in the following example:

  Set rs = db.OpenRecordset("TestTable", dbOpenDynaset, dbSeeChanges)

The dbSeeChanges option ensures that any newly added records that contain a default value in the unique index field are available in the current recordset.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
  Create a module and type the following line in the Declarations section if it is not already there:

     Option Explicit   Type the following procedure:

Function TestSQLData

Dim db As Database, rs As Recordset Dim idx, td        Dim cmd As String

' Delete TestTable if it exists on the SQL server. Set db = OpenDatabase("", False, False,ODBC;dsn= ; _           database= ;uid= ;pwd= ")         cmd = "if exists (select * from sysobjects where _            id = object_id('dbo.TestTable'))"         cmd = cmd & " drop table TestTable"         db.Execute cmd, dbSQLPassThrough

' Create TestTable with one field on SQL server. Set td = db.CreateTableDef("TestTable") td.Fields.Append td.CreateField("Int", dbInteger) td.Fields.Append td.CreateField("String", dbText, 50) db.TableDefs.Append td

Set idx = td.CreateIndex("MyIdx") idx.Unique = True idx.Fields.Append idx.CreateField("Int") td.Indexes.Append idx

cmd = "create Default TestDef3 as 100" db.Execute cmd, dbSQLPassThrough

cmd = "sp_bindefault TestDef3, 'TestTable.Int'" db.Execute cmd, dbSQLPassThrough

' Open table, add a record, and then obtain values. Set rs = db.OpenRecordset("TestTable") rs.AddNew rs!String = "Trial" rs.Update

Debug.Print "RecordCount = " & rs.RecordCount rs.MoveFirst Debug.Print "String is " & rs("String") Debug.Print "Int is " & rs("Int") rs.Close

End Function  To test this function, type the following line in the Debug window, and then press ENTER:

? TestSQLData

Note that run-time error '3167' occurs.

<div class="references_section">