Find the unique "pseudo" index for a linked SQL view in Access

Question

I have an Access database which has a number of linked tables linking to views in a SQL Server database. When you refresh the links to the views, Access asks for the field(s) which contain unique values, in order to build a unique ID index.

My question is, once the link is established, is it possible to see which fields were specified as the unique index? I cannot see anything in the properties of each linked table, or anything in the indexes in design view.

Thanks as always for reading and for any help,

Answer 1

Not only can you get this information, but in theory, if you write your own VBA re-link code, then WHEN you re-link to a different back end (SQL server), then you lose the settings for the PK (primary key value) column chosen at runtime.

In other words, while you can use code (or the Access UI) to re-fresh/relink the table(s) to SQL server "views" in this example? Access WILL remember the settings for PK values, but NOT WHEN you change or point to a different server.

The above information can thus be quite significant, since as a developer, we often will take a SQL backup of the production database, and restore to our local copy of SQL express, and thus use that to "test" and develop our software.

When all is happy and fine, then right before deployment, we will:

Link our front end to the live production database. We then compile the accDB to an accDE, and then the new great version of our software is distributed (installed) to each desktop computer.

However, as I stated, WHEN you relink to a DIFFERENT server and/or database, then you LOSE the PK settings, and thus after a VBA re-link, then those view's in question will become read only.

So, to get/see/find the columns "chosen" at link time?

You can use this code:

    Function ViewPK(strTable As String) As String
    
       Dim db       As DAO.Database
       Dim ix       As DAO.Index
       
       Set db = CurrentDb
       
       For Each ix In db.TableDefs(strTable).Indexes
          If ix.Primary = True Then
             ViewPK = ix.Fields
             Exit For
          End If
       Next
       
    End Function
    

Note that the columns come back as field names (with a "+" prefix), and then each column separated by a ";".

So, if you picked id, and then FirstName, then above would return

 +ID;+FirstName

So, in theory, to get/see, list out the above, then

Sub Test1()

    Dim sResult     As String
    Dim vFields     As Variant
    Dim vF          As Variant
    
    sResult = ViewPK("dbo_FightersV")
    
    vFields = Split(sResult, ";")
    
    For Each vF In vFields
    
        Debug.Print Replace(vF, "+", "")
        
    Next
            
    
End Sub