Where do I find PKs for server views? (1 Viewer)

[kentgorrell]

I'm about to do a thorough testing on Server Views, They've been a bit flaky ever since the RefreshLink issue. I worked my way around all that at the time and everything works... mostly.

This week I imported all my objects including tdf links into a fresh container. After that I had one message telling me I had duplicate PKs for a view. How does that happen?

Now I want to do a thorough review of the methods that I use to link views and create PKs. Rather than me spending hours searching through Msys tables and iterating tdf collections, can anyone point me to exactly where I'd find the PKs for views?

 

[Minty]

You can see them in design view of the Linked View.
If you want to see them in code have a look at the answers here:

Retrieve list of indexes in an Access database

I know there's a way to get a list of all tables in an Access database by using the quering the MsysObjects: SELECT MSysObjects.Name FROM MsysObjects WHERE Left$([Name],1)<>'~' AND Left$...

stackoverflow.com

 

[kentgorrell]

Thanks Minty,

OK, so the View's "Primary Key" is just an index not a constraint. Or is it? Why do they call it a PK? Why don't they just call it a unique index? Why is the index name prefixed with double underscores (another question entirely).

We know that when you create a PK constraint in Access that Access automatically creates a unique index on the PK's column(s). And that if you create a FK then Access automatically creates a corresponding, but for some bizzare reason hidden, index on the FK's column(s).

So do we know if Access creates a constraint for the View's PK or just the unique index?

At least now I know to look for the index.

 

[Minty]

Access thinks a SQL View is a linked table, and pretty much treats it as such.
It requires the PK to be identified so that you can edit it.
You can ignore the request to "Identify a Primary Key" when you link it, and it won't matter, it will just be read only.

However, if you incorrectly set a field as a unique identifier when you link it you can get some very weird outcomes, duplicate rows and all sorts of oddness occurring.

I believe it only creates a local index not a constraint as that would have to be created and enforced on the server not within Access.