none
Linking More than One Tables from SQL Server Makes the Form/Query Not Updateable RRS feed

  • Question

  • Hello,

    I am using Access 2013 and linking to tables in SQL Server 2008. What I want to do is join the tables and make updates to them from a form. The problem is, if I link more than one table — and it does seem to matter which one — I get the message, "This Recordset is not updateable."

    I have three tables: 

    Main Table  Address Table Email Table
    PersonID -  Primary Key AddressID - Primary Key  EmailID - Primary Key
    PersonName Address EmailAddress
    PersonID PersonID

    When I join the Main to Address on PersonID, in Access, I can manually update any of the fields. If I add the Email table, by joining the PersonID to the Main table, I then get the "not updateable error."
    Doing it the opposite way, I join only the Main to Email on PersonID and I can manually update any of the fields. As soon as I add Address (PersonID to Main table) then I get the message again. 

    I set up the Foreign Keys to PersonID in SQL Server. I reduced my tables down to just the fields in the example to make sure there wasn't something else going on.

    Any idea what I am doing wrong? Does joining more that one SQL Server table make the recordset not updateable?

    If there is a VBA Solution, I am comfortable with that. I just thought this should be pretty straight-forward without VBA.

    Thank you for the help!

    ~J



    • Edited by Jenna_Fire Wednesday, May 17, 2017 2:43 PM formatting
    Wednesday, May 17, 2017 2:33 PM

Answers

  • All,

    Thank you for your responses, I really appreciate the help. I was able to find the solution.

    In SQL Server , for each of the sub-tables (Address and Email), I added a unique Index to the PersonID field. That did it!

    I understand that usually a form is based on one table and then you have subforms. The way this was originally set up, I needed to have a query with multiple tables and that query as my recordsource. It worked when the tables were in Access, when I moved them to SQL Server, then it didn't work the same way as I was expecting it to.

    ~J 

    • Marked as answer by Jenna_Fire Thursday, May 18, 2017 5:33 PM
    Thursday, May 18, 2017 5:33 PM

All replies

  • Hi Jenna,

    The easiest way to avoid inconsistent updates is to simply update one table at a time. The reason you're probably getting an unupdatable recordset is because joining multiple tables creates duplicate rows for one of the tables.

    Just my 2 cents...

    Wednesday, May 17, 2017 3:42 PM
  • You didn't say what fields you were trying to update.

    Build a little, test a little

    Wednesday, May 17, 2017 5:49 PM
  • As a general rule you base a form on ONE table.

    So you might have a main form with personname/PersonID.

    Then drop in a sub form to allow you to add “many” address.

    And you can drop in another sub form to allow you to add “many” emails.

    So while you have what looks like “one” form, it will in fact be 3 forms.

    The main form (personName)
    The address sub form (allows adding of many address)
    The email sub form (allows adding of many emails).

    So while the user sees one form, each part of the form needs to be based on one table. And yes, in a large number of cases if you based a form on multiple tables, then that form is read only and you can't update the single query. So base each of the 3 above parts on the base table - don't use any query with multiple tables.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, May 17, 2017 7:24 PM
  • All,

    Thank you for your responses, I really appreciate the help. I was able to find the solution.

    In SQL Server , for each of the sub-tables (Address and Email), I added a unique Index to the PersonID field. That did it!

    I understand that usually a form is based on one table and then you have subforms. The way this was originally set up, I needed to have a query with multiple tables and that query as my recordsource. It worked when the tables were in Access, when I moved them to SQL Server, then it didn't work the same way as I was expecting it to.

    ~J 

    • Marked as answer by Jenna_Fire Thursday, May 18, 2017 5:33 PM
    Thursday, May 18, 2017 5:33 PM