Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server Linked Tables (Access 2000 SR1)

    I have an Access front-end, with linked tabled from a SQL Server database. One of my linked tables in Access isn't updatable -- that's the problem.

    All of my linked tables in Access are actually Views in SQL Server, and all but the one is updatable. The View, in SQL Server is updatable, but it's not in Access.

    I cannot figure out why this one linked table would be different (un-updatable) from the others.

  2. #2
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Linked Tables (Access 2000 SR1)

    I figured it out!
    When I linked the View, somehow I did not designate a Primary Key column. Without a PK, you cannot update the table.

    Thanks for all your help, everyone! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  3. #3
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Linked Tables (Access 2000 SR1)

    Weird: when I update the tables with Linked Table Manager, the SQL Server table updates correctly but the SQL Server View drops the PK designation.
    So, every time I update this linked SQL Server View (as a table in Access) I end up having to delete the linked table (deleting any relationships along the way) and then re-link it from scratch.

    I think this is a BUG in MS Access!

    (BTW: I have to update the link because I made to modify the base table -- add a column or something.)

    If anyone has words of wisdom or guidance for this situation, I'd love to read them!

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Server Linked Tables (Access 2000 SR1)

    Relationships? Are you creating relationships in the front end, and if so, why?
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Server Linked Tables (Access 2000 SR1)

    This has been an issue with Access and linked SQL Server views for some time, but I believe the culprit is actually the ODBC driver and/or the MDAC. It became more of an issue in A2K because one of the basic rules for updatability of linked tables is that there must be a primary key. We resorted to creating a specific set of instructions for deploying a new version of a database front-end that included dropping and readding the connections to linked views, all because we have a test SQL DB and a live SQL DB. BTW, relationships do really get in your road in this case, and don't provide much value other than convienence in constructing queries. Relational constraints will need to be set in the SQL database, as the ODBC driver effectively bypasses any enforced referential relationships.

    If you get really frustrated with the situation, you could try looking at an ADP - there are many pros and cons, but you are now working directly with the SQL tables.
    Wendell

  6. #6
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Linked Tables (Access 2000 SR1)

    Your retort, Charlotte, is not helpful -- it does not address the real issue of my posting.
    I notice that you're more prone to shoot questions back at people seeking help than you are to provide helpful advice. That's too bad.

    Wendell's reply to my post was much more helpful. He started off by addressing the issue that I brought up -- not some side issue. Only after he addressed the core issue did he make a comment about the Relationship issue.

    Now, to answer your question, the relationships are a hold-over from before I upsized from Access to SQL Server. And I have kept them only for convenience -- they are especially helpful to other users when doing ad-how querying.

  7. #7
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Linked Tables (Access 2000 SR1)

    Thank you, Wendell, for that helpful information.
    Do you know if there is an ODBC or MDAC version that 'fixes' this problem?

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Server Linked Tables (Access 2000 SR1)

    I'm not sure which problem you are referring to. The one with views not having primary keys will probably never be fixed - it's an ODBC issue and not the latest technology, which is ADP. As to the referential integrity, it can't be fixed as the tables are no longer managed by the Jet software, and it thus has no knowledge as to the ER model being used.
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Server Linked Tables (Access 2000 SR1)

    It is hardly unusual to try to understand the question and the purpose of it before attempting an answer. Since Jet relationships have nothing at all to do with SQL Server, I was understandably confused by your reference to them. It was, after all, your own side issue, not one I introduced.

    I'm glad that Wendell's response was of help to you. He is one of our real experts in combining Access with SQL Server, so I'm not surprised he was able to zero in on your problem.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •