none
Updating SQL Server View from MS Access: what makes a recordset read-write?

    Question

  • I have a MS Access database with a Linked table to a SQL Server View.

    On one SQL Server database (Dev), it works fine and I can update the columns from my main table (the view is one main table with four lookup joined with LEFT joins).
    I had initially not done anything special to make the linked view updateable...

    When I point my linked tables to my Test server (should be identical), my linked view becomes read-only...

    This article shows how to create a "pseudo" index on the MS Access Side and it fixed my problem...

    BUT WHY???

    Why pointing to a different server changes my MS Access linked table/view from Read-Write to ReadOnly???

    PS: I tried pointing to another Dev server and it was fine


    Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net

    Friday, May 23, 2014 3:26 PM

Answers

  • Hello,

    Did the view on the Test server as same as the table on Dev server?

    Just as the KB article your post interprets: If your server supports updating through views, you need to create an index specification on the attachment to tell Access which fields uniquely specify a record returned by the view. This lets the Jet database engine create an updatable recordset on the view, and on Access queries and forms that use it.

    If the view on the Test server do not have primary keys (or a unique index),Access will prompt you for the fields to use so it can build a local index when you create link to the view . If you skip that step the link will be read-only.
    If you did select the appropriate fields then the link will be editable but refresh that link later, either in code or with the linked table manager that information for the local index is discarded and the link reverts to read only. To solved this issue, please try to to add a Primary keys or unique index to the view on the SQL Server.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here. 


    Fanny Liu
    TechNet Community Support


    Monday, May 26, 2014 9:05 AM

All replies

  • Hello,

    Did the view on the Test server as same as the table on Dev server?

    Just as the KB article your post interprets: If your server supports updating through views, you need to create an index specification on the attachment to tell Access which fields uniquely specify a record returned by the view. This lets the Jet database engine create an updatable recordset on the view, and on Access queries and forms that use it.

    If the view on the Test server do not have primary keys (or a unique index),Access will prompt you for the fields to use so it can build a local index when you create link to the view . If you skip that step the link will be read-only.
    If you did select the appropriate fields then the link will be editable but refresh that link later, either in code or with the linked table manager that information for the local index is discarded and the link reverts to read only. To solved this issue, please try to to add a Primary keys or unique index to the view on the SQL Server.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here. 


    Fanny Liu
    TechNet Community Support


    Monday, May 26, 2014 9:05 AM
  • Hi Fanny,

    Yes, the view is exactly the same on Dev and Test but I do refresh the links to point at Test so this might be when the recordset reverts back to Read-Only.

    When you add an "Access index" to the linked table, does this get affected if we refresh the connection details through either "Linked Table Manager" or "VB Code"?

    Thanks

    Eric


    Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net

    Monday, May 26, 2014 5:02 PM