Relinking to SQL Server 2000 Updateable Views - Choosing a Primary Key

[klgrube]

Hi!

We have an ACCESS 2000 database which has links to several SQL Server view and tables. The tables all have primiary keys, and when you go to relink them, ACCESS doesn't take you to the "Select Unique Identifier" dialog box. However, when you relink a View, ACCESS takes you to the "Select Unique Identifier" dialog box, even though the underlying table's record key is part of the underlying view. Our views are currently only view to single tables, so it's not a question of viewing data from multiple tables. We will eventually need to relink all tables and views programmatically (on the "on open" event of the database), but won't be able to do this if the views won't automatically select the primary key the way the tables do.

Is there a way to set a primary key on a SQL Server 2000 view? Is there a way to tell ACCESS to use the underlying table's primary key? In other words, is there a way to avoid that dialog box when linking to a view? By the way, we are using these views as the data source on several forms, and the updates from those forms work fine when you have selected a correct primary key when you link the view.

Any suggetions would be greatly appreciated.

THANKS!!
Karen Grube
kgrube@ffres.com
klgrube@aol.com

 

[cmmrfrds]

Have you considered using pass through queries and avoid linking the sql server tables. Pass through queries can be the data source of a Form or Report.

 

[klgrube]

Hi!

Thanks for the suggestion! Yes, we've considered it. It's not that we can't do that. We can if we have to. It's just that this should work.

What's happening now is this: I've indexed the view on the primary key. But when we re-link the view and try to update the data in datasheet view, we get an error stating:

"ODBC -- Update on a linked table 'dbo.test' failed.

Update failed because the following SET options have incorrect settings: 'ARITHABORT',(#1934).


I have learned that you have to set the ARITHABORT option to ON whenever you want to do an update to an indexed, linked view, but I don't know where to set that option within Access. There doesn't seem to be a place to tell ACCESS to set the default for a table / database to "SET ARITHABORT ON".

Any suggestions?

Thanks!!!
Karen

 

[cmmrfrds]

It talks about the problem in this link.

http://www.winscriptingsolutions.com/Articles/Index.cfm?ArticleID=26605

You could set the option in a pass through query by batching the option settings with the sql statement.

An Access mdb is limiting when SQL Server is the back end database, it is much better to go with an Access Project which was set up as a front end to SQL Server.