PRB: Error 3219 When Updating Record Set Created w/ Distinct

- Microsoft Visual Basic programming system for Windows, version 3.0

SYMPTOMS

Trying to perform an update on a result set created with the Distinct predicate results in error 3219 "Can't perform operation, it is illegal."

CAUSE

An object variable or a data control result set created with the Distinct predicate is not updatable according to Help. The following statement is in the Distinct keyword Help topic:

   The output of a query that uses Distinct is not updatable and doesn't
   reflect subsequent changes made by other users. Therefore, when you use
   the Distinct predicate in a query, you are prevented from trying to
   update your records.

WORKAROUND

The only workaround at this time is to not use the DISTINCT predicate to build the results set. Note that you may have to handle the duplicates by some other coding means.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Problem

1. Start Visual Basic or from the File menu, choose Open Project(ALT, F, O) if Visual Basic is already running. Form1 is created by default.

2. Add a data control, two command buttons, and one text box to Form1.

3. Using the following table as a guide, set the properties of the controls you added in step 2.

      Control Name   Property       New Value                     Comment
      ------------------------------------------------------------------------
      Command1       Caption        "Set Up Distinct Predicate"
      Command2       Caption        "Press for Update"
      Data1          DatabaseName   BIBLIO.MDB                    Provide the
                                                                  full path to
                                                                  this file,
                                                                  which should
                                                                  be in C:\VB
      Data1          RecordSource   Authors
      Text1          DataSource     Data1
      Text1          DataField      Author
   
   

4. Add the following code to Command1 click event procedure:

      Sub Command1_Click ()
         '* Enter the following two lines of code as one, single line:
         data1.RecordSource = "Select DISTINCT Author From authors
            where author > 'a'"
         data1.Refresh
      End Sub
   
   

5. Add the following code to Command2 click event procedure:

      Sub Command2_Click ()
         data1.Recordset.Update
      End Sub
   
   

6. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. Click the Command1 button to set up the Distinct predicate. Delete the zero in "Arnson, Robert, 1970." Then click the Command2 button. This should result in the 3219 error "Can't perform operation, it is illegal."