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

ID: Q104459


The information in this article applies to:


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."


Additional query words: 3.00


Keywords          : 
Version           : 
Platform          : 
Issue type        : 

Last Reviewed: June 8, 1999