PRB: Error When Updating Fields in Dynaset That Has 2+ Tables

Last reviewed: August 8, 1996
Article ID: Q102681
The information in this article applies to:
  • Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit only, for Windows, version 4.0, using the DAO 2.0/2.5 Compatibility Library
  • Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SYMPTOMS

When trying to edit or update fields in a dynaset that was created by a SQL select statement that joined two or more tables, the following errors may occur. In these messages, 'item' is a field in a table to be changed.

   Can't perform operation; it is illegal. (3219)
   Can't update 'item'; field not updatable. (3113)

CAUSE

These errors occur if the Microsoft Access engine cannot ensure that referential integrity of the table entries will be maintained as a result of the operation.

MORE INFORMATION

For a multiple table dynaset to be updatable, the following must be true:

  • The dynaset needs to have been created with a SQL "join" clause between tables that have a one-to-many relationship.
  • There must be a unique index (or primary key) on the one-side of the query.

Reproducing the Behavior

These examples use the BIBLIO.MDB database that shipped as a sample database with Visual Basic version 3.0 for Windows. This code will only work on a database that doesn't have referential integrity enabled, such as the BIBLIO.MDB from Visual Basic 3.0. In BIBLIO.MDB, the Authors table has a unique (primary) index set on AU_ID, and [Title author] has an index set on AU_ID but it is not unique or primary. The following code causes the errors:

   Dim db As database
   Dim ds As dynaset
   ' The following SQL$ code is correct. It will not generate an error.
   ' Enter it as one, single line:
   ' SQL$ = "Select * from AUTHORS,TITLES, Titles INNER JOIN
   '    authors on Titles.AU_ID = Authors.AU_ID"
   ' The following line will cause the error, but it won't be generated
   ' until the last line of the same code -- which is expected:
   SQL$ = "Select * from AUTHORS,TITLES where Titles.AU_ID = Authors.AU_ID"
   Set db = OpenDatabase("C:\vb3\biblio.mdb")
   Set ds = db.CreateDynaset(SQL$)
   ds.Edit

This is a classic example of a SQL inner join statement. It chooses all fields from both tables where the book titles match up with the author who wrote them. The unique index is the ID number of the author. This means one author can have many titles but books by a single author will have only one author in the Authors table.

If this query did not have a one-to-many relationship, the error, "Can't perform operation; it is illegal" (3219) would occur on the line "ds.Edit." The error is telling you that either there is not a unique index in the multiple-table dynaset, or there is no unambiguous one-side of the one to many relationship to the query. Checking the updatable property of the dynaset before invoking edit mode avoids the error from attempting to edit a non-updatable dynaset.

After the query is successfully created and the copy buffer is opened by issuing the Edit statement, you can proceed with updating records.

   ds.Fields("Title") = "Some new book title"
   ds.Update

This works because "Title" is on the non-unique or many-side of the initial query. All the records in the Titles table are editable whereas none of the records in Authors table are editable. The error "Can't update 'item'; field not updatable." (3113) occurs with an attempt to edit any item in the Authors table.


Additional reference words: 3.00 4.00 Jet 2.0 2.5 vb416
KBCategory: kbprg kbprb
KBSubcategory: APrgDataAcc



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 8, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.