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

ID: Q102681

The information in this article applies to:

Microsoft Visual Basic Professional and Enterprise Editions, 16-bit only, for Windows, version 4.0
Microsoft Visual Basic Professional Edition 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 query words: 3.00 4.00 Jet 2.0 2.5 vb416

Keywords : APrgDataAcc
Version : 3.00 4.00
Platform : WINDOWS
Issue type :