ACC: New SQL Records Appear Deleted Until Recordset Reopened

Article translations Article translations
Article ID: 135379 - View products that this article applies to.
This article was previously published under Q135379
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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.

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

For more information about the OpenRecordset method, search the Help Index for "OpenRecordset," and then "OpenRecordset method," or ask the Microsoft Access 97 Office Assistant.

Properties

Article ID: 135379 - Last Review: September 27, 2013 - Revision: 3.2
APPLIES TO
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Keywords: 
kbnosurvey kbarchive kberrmsg kbprb kbusage KB135379

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com