ACC: New SQL Records Appear Deleted Until Recordset Reopened

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

When you add a record to an SQL table 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 using Visual Basic code, include the dbSeeChanges option, as in the following example:

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

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a module and type the following line in the Declarations section if it is not already there:

         Option Explicit
   

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

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

         ? TestSQLData()
   

   Note that run-time error '3167' occurs.

REFERENCES