Microsoft Knowledge Base Email Alertz

KBAlertz.com: (135379) - 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...

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.

Click here to create a
FREE account
Already have an account?
[Click here to Login]

Search KbAlertz

Advanced Search

Webmasters
Put kbAlertz on your website.
[ Click Here for more! ]






Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks

ACC: New SQL Records Appear Deleted Until Recordset Reopened

Retired KB ArticleThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Article ID:135379
Last Review:January 19, 2007
Revision:3.2
This article was previously published under Q135379
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.

Back to the top

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.

Back to the top

STATUS

This behavior is by design.

Back to the top

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.

Back to the top

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.

Back to the top


APPLIES TO
•Microsoft Access 95 Standard Edition
•Microsoft Access 97 Standard Edition

Back to the top

Keywords: 
kberrmsg kbprb kbusage KB135379

Back to the top

   

Community Feedback System

Very often, it takes hours to solve a problem. Very often, you've looked high and low, and have tried a lot of solutions. When you finally found it, chances are, it was because someone else helped you. Here's your chance to give back. Use our community feedback tool to let others know what worked for you and what didn't.

Please also understand that the community feedback system is not warranted to be correct, it's simply a system that we've built to let people try and help each other. If something in a feedback response doesn't make sense to you, or you're not comfortable making changes that the feedback talks about (like registry edits), please consult a professional.

Thank you for using kbAlertz.com Feedback System.

-- Scott Cate

Be the first to leave feedback, to help others about this knowledge base article.

(Optional) Name

(Optional) Public URL Or Email

Comments
No HTML -- Text Only Please