ACC: New SQL Records Appear Deleted Until Recordset Reopened (135379)

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.

MORE INFORMATION

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=<datasource>; _
            database=<database>;uid=<user id>;pwd=<password>")
         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.