Relinking 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

 

[jfischer]

I don't have any documentation for Jet 4.0, but here's some information from Jet 3.0 that might help.

http://msdn.microsoft.com/library/d...etDatabaseEngineVersion30ODBCConnectivity.asp

Jet Database Engine Version 3.0: ODBC Connectivity

Quoting from the document:

A server view may be linked but will be treated exactly like a linked table with no indexes. Thus a linked view, and any query based on one, will be a non-updatable snapshot. However, if you know that certain columns uniquely identify rows in the view (perhaps they comprise a unique index in the underlying table), you can create a pseudo unique index on the linked table itself, by using a DDL query, such as:

  CREATE UNIQUE INDEX Index1
 ON LinkedTable (Column1, Column2)

Do not make this a SQL Pass-through query; this does not create an index on the server's table or view. But it tells Jet how to uniquely identify rows, and allows dynaset functionality such as updating. Server-based Stored Procedures may not be linked because these do not resemble tables and views closely enough.