ACC: New SQL Records Appear Deleted Until Recordset Reopened

This article was previously published under Q135379
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

When you add a record to an SQL table by using Visual Basic forApplications, if the table's unique index field has a default value, andyou do not assign a value to that field, the new record appears deleteduntil you reopen the SQL table. If you try to obtain a value from the newrecord, you receive the following error message:
Run-time error '3167'
Record is deleted.
When you open the SQL table by using Visual Basic code, include thedbSeeChanges option, as in the following example:
   Set rs = db.OpenRecordset("TestTable", dbOpenDynaset, dbSeeChanges)				

The dbSeeChanges option ensures that any newly added records that containa default value in the unique index field are available in the currentrecordset.
This behavior is by design.

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.
For more information about the OpenRecordset method, search the Help Indexfor "OpenRecordset," and then "OpenRecordset method," or ask the MicrosoftAccess 97 Office Assistant.
run time error 3167

Article ID: 135379 - Last Review: 09/27/2013 17:51:31 - Revision: 3.2

  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kbnosurvey kbarchive kberrmsg kbprb kbusage KB135379