Azure SQL Table -> SQL Server View -> Linked Access table: can't update

Question

So I have this cute daisy chain:

• A Table in an Azure SQL Database (tblAzure)
• A View to tblAzure in a local SQL Server database (vwSQL)
• A linked table in Access pointing to vwSQL (tblAccess)

Although parts work individually, I can't update the Azure table from Access.

The connection between the databases work properly individually. I can update tblAzure by inserting values into vwSQL. I can also update tables in SQL server via linked Access tables, or even tables by creating a view to them and linking Access to that view. I can also see the values in tblAzure through vwSQL opened in Access as a linked table.

This is the error I get back when I try to update or append the linked view:

ODBC--insert on a linked table 'tblAccess' failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLNCLI11" for linked server "azw" returned message "The parameter is incorrect:.".(#7412)
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLNCLI11" for linked server "azw" reported an error. One or more arguments were reported invalid by the provider. (#7399)
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "azw" was unable to begin a distributed transaction. (#7391)

Anyone seen anything like this? I tried changing the link properties/Server Options / Distributor to True, it didn't help.

The basic idea is that I need a table that is perfectly synced in both our databases, and one that can be edited by our users in Access. (Don't ask, I inherited a way too overcomplicated system...)

---

Test scripts

In Azure:

CREATE TABLE [dbo].[AzureTable](
    [AzureTableID] [int] NOT NULL,
    [SomeText] [nvarchar](50) NULL,
 CONSTRAINT [PK_AzureTable] PRIMARY KEY CLUSTERED ([AzureTableID] ASC)
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

INSERT dbo.AzureTable VALUES (1, N'Hello')

In SQL Server:

(AZW is the name of the linked Azure server, HUFU is the DB)

CREATE VIEW dbo.SQLServerView
AS
SELECT *
FROM AZW.HUFU.dbo.AzureTable
GO

INSERT INTO dbo.SQLServerView values (2,'working')

This is working too.

In Access, I link the view (I have many other linked tables and views, they all work). Opening the Access linked table (pointing to the SQL server view) it shows the data, but can't save the modifications, displaying the above mentioned error.

I actually have a workaround now, but this thing is bugging me, I'd love to understand what's wrong here...

Haem

Interesting setup :p -- To rule out problematic data types, can you try the same with a very simple table, e.g. a Long Int PK and a Varchar(50) column ? – Courtesan

@Courtesan I use int PK as Access can't handle SQL Server's long, but I'll try a very simple table when I get to it and report back, thanks for the idea. – Haem

@Courtesan I have created a very simple setup, but I get the same error. I'll add the scripts to my question in a bit. – Haem

If you open the design view of SQLServerView in Access, does it show AzureTableID as PK? Or: when you linked the view, did Access ask you to select the PK? Or set it automatically? --- But actually, from the ODBC error messages, I doubt that a missing PK in Access is the problem - what you are trying may simply be impossible. – Courtesan

@Courtesan Access asked me to select a PK, so I did. I'm also starting to think this is impossible. There is some protocol incompatibility maybe between the systems. – Haem

Answer 1

I am not sure this will apply to your case but the last time I was unable to update an SQL Server View from MS Access the solution was to make sure the Access linked table that represented the view had a primary key.

This is what I use to create the PK:

CurrentDb.Execute "CREATE INDEX __uniqueindex ON [" & TableName & "](" & PKFieldName & ")"

Blab

Hmm, my original table has an index, but I'll try explicitly specifying an index for my view and report back... Thanks for the idea. – Haem

Well, it turns out I can't create an index on a view that is referencing a table in a different database. To index a view, I'd have to schema-bind it, but schema binding only works within the same database. Bummer. – Haem