SQL Server Linked Tables (Access 2000 SR1)

Topic

New Reply

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.

Reply | Quote

Replies

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!

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote