none
This recordset is not updatable - Access 2010 with linked tables from SQL Server 2008

    Question

  • I cannto type in the textbox for which the control source=QCNote. What is wrong with my query to make the recordset unupdatable?  My form_open event is as below:

    Private Sub Form_Open(Cancel As Integer)

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = CurrentProject.AccessConnection
    Set rs = New ADODB.Recordset
    With rs
    Set .ActiveConnection = cn

    DocID = [Forms]![QCDocAttributes]![DocID]

    DocumentType = [Forms]![QCDocAttributes]![Document Type]

    strSQL = "SELECT " & DocID & " AS DocID,'" & DocumentType & "' AS DocumentType,     QC_QCDecisionPoint.Description, QC_QCDecisionPoint.QCDecisionPointID , QC_QCResultDecisionPoint.QCNote FROM QC_QCResultDecisionPoint RIGHT JOIN ((QC_QCAttribute INNER JOIN QC_QCAttributeDecisionPointAsc ON QC_QCAttribute.QCAttributeID = QC_QCAttributeDecisionPointAsc.QCAttributeID) INNER JOIN QC_QCDecisionPoint ON QC_QCAttributeDecisionPointAsc.QCDecisionPointID = QC_QCDecisionPoint.QCDecisionPointID) ON QC_QCResultDecisionPoint.QCDecisionPointID = QC_QCDecisionPoint.QCDecisionPointID WHERE (((QC_QCAttribute.Description)= '" & [Forms]![QCDocAttributes]![AttributesDropdown] & "' ));"

    .Source = strSQL
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
    End With
    Set Me.Recordset = rs

    Set rs = Nothing
    Set cn = Nothing

    End Sub

    Friday, January 18, 2013 3:50 PM

Answers

  • I tried to use the query directly as the record source for the form, but it caused Access to do a partial auto-save every time I navigated to another record, or closed the form (in addition to my save button event).  For now,  I am deleting the incomplete records from the Result table, when the form is closed.

    I don't follow what you mean by a "partial auto-save".  Access will always save a dirty (that is, modified) record when you move to a new record or close the form.  That's a feature that has nothing to do with ADO recordsets, SQL Server back-ends, or anything else that is special about your situation.  If you want to be able to edit multiple records and save them all in a batch, it's a much more complicated process -- the most common method is to load the records into a temp table, use the form to edit that table, and then have a command button to apply the updates from the tamp table to the records in the original source table(s).

    As for "partial" save, that may be a result of your query -- since it appears to involve tables in a many-to-one relationship, if you edit the value of a field in the "many" table, that's the only record Access will save.  You may need to use code in  the form's BeforeUpdate event to copy field values from the one-side table(s) to corresponding fields in the many-side table.  Either that, or set up your form as a main form / subform arrangement, with Link Master/Child Fields set to force the linking values into the child form.  But I'm not completely sure what's going on; this is all just a guess.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by RSNoobie Wednesday, January 23, 2013 9:05 PM
    Tuesday, January 22, 2013 5:49 PM

All replies

  • Hi RSNoobie,

    here you can find some  suggestions http://allenbrowne.com/ser-61.html


    Cinzia



    Sito RIO
    Il mio Blog


    Friday, January 18, 2013 4:38 PM
  • Thanks Cinzia.  I have gone over Allen Browne's list over and over again and fail to understand why my recordset is not updatable. 

    Of all the reasons mentioned, I feel that the most likely culprit would be the database/table design.  However, all the tables in the query have primary keys and all the tables have relationships between them.   Is it possible to post a database diagram here to clarify?

    • Proposed as answer by RO1314559 Friday, January 18, 2013 8:38 PM
    Friday, January 18, 2013 5:14 PM
  • You are closing your rs object. I suggested in a cross-post that you need to make rs and cn global variables and leave them open while the form is open.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, January 18, 2013 10:44 PM
  • Why not simply use ODBC linked tables?
    Saturday, January 19, 2013 12:29 AM
  • What is your exact error?  Check your strSQL:

    In the Immediate Window: ?strSQL

    What is the result?

    That should tell you what the problem is.


    Ryan Shuell

    Sunday, January 20, 2013 4:33 PM
  • You are closing your rs object. I suggested in a cross-post that you need to make rs and cn global variables and leave them open while the form is open.

    Bill, reading the code posted, I don't see where the recordset object rs is being closed.  It's only being set to Nothing, which isn't the same thing at all, since the form's Recordset property has already been set to the opened recordset, and so a reference to the recordset is being maintained.  I don't think this is the problem, though I haven't yet figured out what the actual problem is.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, January 20, 2013 6:00 PM
  • To debug this, maybe it would be helpful to link to the tables using ODBC, and try the query directly to see if it is updatable, without any question of whether the dynamic setting of the form's recordset to an independently opened ADO recordset is a factor.

    I notice that you don't include the primary key of QC_QCResultDecisionPoint in the query's result set.  I wonder if that is the cause.  Are any other fields, such as QC_QCDecisionPoint.Description, updatable?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, January 20, 2013 6:10 PM
  • Dirk - You are absolutely correct. I've tried to use an ADO Recordset as a form's RecordSet and can't get an updatable set whether I set rs/cnn to nothing or not.

    According to this MS Support article RS is doing it correctly. I tried tables with or without an IDENTITY column. All had a primary key. I just can't get it to work. The form's fields fill and I can navigate but can't edit.

     

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, January 21, 2013 4:21 PM
  • Why not simply use ODBC linked tables?

    One reason would be to eliminate exposing the table in any way except through the form. No matter how well you lock down an Access application a clever user can still get to the tables.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, January 21, 2013 4:24 PM
  • Thank you all for your suggestion to help me with this one.

    Actually, all the QC_ tables in the query are linked tables from SQL Server 2008.  How do I use them directly to populate the form?

    Is it the right join in the query that is making it unupdatable? 

    Tuesday, January 22, 2013 5:33 PM
  • Dirk,

    Thank you for  your reply.  I tried to use the query directly as the record source for the form, but it caused Access to do a partial auto-save every time I navigated to another record, or closed the form (in addition to my save button event).  For now,  I am deleting the incomplete records from the Result table, when the form is closed.

    Tuesday, January 22, 2013 5:36 PM
  • I tried to use the query directly as the record source for the form, but it caused Access to do a partial auto-save every time I navigated to another record, or closed the form (in addition to my save button event).  For now,  I am deleting the incomplete records from the Result table, when the form is closed.

    I don't follow what you mean by a "partial auto-save".  Access will always save a dirty (that is, modified) record when you move to a new record or close the form.  That's a feature that has nothing to do with ADO recordsets, SQL Server back-ends, or anything else that is special about your situation.  If you want to be able to edit multiple records and save them all in a batch, it's a much more complicated process -- the most common method is to load the records into a temp table, use the form to edit that table, and then have a command button to apply the updates from the tamp table to the records in the original source table(s).

    As for "partial" save, that may be a result of your query -- since it appears to involve tables in a many-to-one relationship, if you edit the value of a field in the "many" table, that's the only record Access will save.  You may need to use code in  the form's BeforeUpdate event to copy field values from the one-side table(s) to corresponding fields in the many-side table.  Either that, or set up your form as a main form / subform arrangement, with Link Master/Child Fields set to force the linking values into the child form.  But I'm not completely sure what's going on; this is all just a guess.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by RSNoobie Wednesday, January 23, 2013 9:05 PM
    Tuesday, January 22, 2013 5:49 PM
  • If you want to update more than one table with a SQL Server back end, use a view with an instead of trigger.

    See the Overcoming Query Updatability Limitations section in the following link.

    http://msdn.microsoft.com/en-us/library/bb188204.aspx
    Tuesday, January 22, 2013 6:07 PM
  • Dirk,

    I have been reading through your post again trying to understand how I can get Access to save every field on the form in the Result table instead of the just the updated fields.  There are 3 tables that I am using on the form:

    QCAttribute which has AttributeID

    QCResultAttribute which has the associated decision points for the Attribute and has a unique ResultAttributeID for every affected AttributeID/DecisionPointID combo.

    QCResultDecisionPoint which has the QCNote for each affected decision point. (PK=QCResultDecisionPointID)

    The QCResultDecisionPoint table has the following fields:

    QCAssignmentID

    QCDecisionPointID

    QCResultAttributeID

    QCNote

    When Access auto saves, it only inserts QCResultAttributeID and QCNote.  How do I write code in the form's BeforeUpdate event to fill in QCAssignmentID and QCDecisionPointID values?

    The control source query for the form is as follows:

    SELECT B.QCDecisionPointID, B.Description AS [Decision Point], C.QCResultDecisionPointID, C.QCNote, C.QCResult, C.NoteText, C.QCResultAttributeID, A.QCAttributeID, [Forms]![QCDocAttributes]![QCResultAttID] AS QCResultAttID, [Forms]![QCDocAttributes]![AssignmentID] AS QCAssignmentID
    FROM (QC_QCAttributeDecisionPointAsc AS A INNER JOIN QC_QCDecisionPoint AS B ON A.QCDecisionPointID = B.QCDecisionPointID) LEFT JOIN (SELECT * FROM QC_QCResultDecisionPoint WHERE QCResultAttributeID=[Forms]![QCDocAttributes]![QCResultAttID])  AS C ON B.QCDecisionPointID = C.QCDecisionPointID
    WHERE (((A.QCAttributeID)=[Forms]![QCDocAttributes]![AttributeID]));

    Perhaps these images will help to give a better picture:

    When I submit changes, I see two records in the QCResultDecisionPoint table, one from my Save button event, the other (partial) due to Access Auto Save.  I am unable to post more than two images in this message, so imagine the result table with a primary key that has alternate values.

    Thank you. 




    • Edited by RSNoobie Tuesday, February 19, 2013 6:20 PM
    Tuesday, February 19, 2013 5:50 PM
  • For those who are looking for a solution to this, I got the answer at :

    http://www.access-programmers.co.uk/forums/showthread.php?p=1235277&posted=1#post1235277

    The code I had it in the Submit button, I put it in the BeforeUpdate event.  That did the trick.

    Wednesday, February 20, 2013 4:14 PM