[AccessD] Linked ODBC Views - rebuilding the PK after the connection refresh

As discussed previously I am building a small framework for rebuilding the connection of ODBC 
objects linked to SQL Server, the objective being to allow me to switch database names or even 
server names on-the-fly.

What I have discovered is that I can do this for tables without an issue but when I try to do so for 
linked views they lose the PK index in Access, which makes them read-only.

IOW When you manually link to a view out in SQL Server, it asks you to specify what field in the 
view is the PK

Using a combination of Lambert's code to get the name of the index as well as the index field and 
Jim's suggestion for the SQL to create the index after refreshing the tdf, I now have my index back 
and the linked views remain editable.

I have not tested this extensively, for example Lambert's code assumed a single field PK and thus 
would not correctly recreate a multi-field PK but given that I always use surrogate PKs anyway, it 
works for me.  I will probably have to fix that eventually since my systems at the client do link to 
third party databases and ... well... so much for surrogate PKs.

But for the here and now it is functioning.

Actually modifying Lambert's code to capture all of the fields in a multi-field PK index will be 
pretty trivial anyway.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 2/29/2012 2:47 PM, Jim Dettman wrote:
>
>   Not really sure, but when you link the external table, JET tries to figure
> out the best way to talk with the table.  I would imagine that modifying the
> connect property is being viewed as a new link and it goes through that
> process again.  But why it doesn't end up the same way I can't tell you.
>
>    I'm not sure what it is your doing that's causing the problem, but as part
> of your process after your done re-linking, maybe you should take Lambert's
> code, identify the PK index, Drop it, then use an Create Index command,
> i.e.:
>
> DROP INDEX CategoriesCategoryName ON Categories;
>
> CREATE UNIQUE INDEX LinkCategoryID ON Categories (CategoryID);
>
> To force the field(s) choice in the index.  Note that this doesn't affect
> the table in SQL, your just modifying how the table is described on the
> Access side.
>
> You can do this as well too when JET makes an incorrect choice about the
> fields to be used for the index.
>
> Jim.