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

• 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)

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.

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

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