1

I have a fairly complex Access database, with a SQL Server Azure backend.

I have come to a situation where I need to be able to update data in a view (I'm using an INSTEAD OF UPDATE trigger to capture the updates).

I am able to do this if I add the view manually as it lets me select the primary key (the field "ID" in the view).

But when I add the view as a linked table via VBA it doesn't prompt for the primary key.

I have tried adding a primary key to the view but it's too complex to support this.

My VBA is below:

    Set td = CurrentDb.CreateTableDef(stLocalTableName)
    td.SourceTableName = stRemoteTableName
    td.Connect = stConnect

    CurrentDb.TableDefs.Append td

    If UBound(Filter(g_IndexViews, stRemoteTableName)) > -1 Then
        ' Add index on "ID" column
        Set idxID = CurrentDb.TableDefs(stRemoteTableName).CreateIndex("pk_" & stRemoteTableName)

        With idxID
            .Fields.Append .CreateField("ID")
            .Primary = True
        End With

        CurrentDb.TableDefs(stRemoteTableName).Indexes.Append idxID

    End If

This code throwns an error

3057 - Operation not supported on linked tables

I've also tried it refactored a little by adding the index to the TableDef before I add the tabledef to the list, but then I get the error

3039 - Could not create index; too many indexes already defined

Can anyone think of a way around this problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bendataclear
  • 3,802
  • 3
  • 32
  • 51
  • See if this works for you: https://stackoverflow.com/questions/32316669/how-to-programmatically-create-an-odbc-linked-table-to-a-sql-server-view-and-hav -- it uses Sql `CREATE INDEX` to create the PK on the linked view. – Andre Sep 02 '19 at 14:57
  • @Andre Does this create it locally? Or actually on the server? – bendataclear Sep 02 '19 at 15:01
  • @Andre I tried it and it worked, didn't realise this would make it work locally, thank you, add as an answer and I'll accept. – bendataclear Sep 02 '19 at 15:07
  • 1
    I wasn't 100% sure it would work for your view, if the DAO approach didn't work - cool that it does! imho we should close this as duplicate, no reason to replicate the answer here. – Andre Sep 02 '19 at 15:21

0 Answers0