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
This 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
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
