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...

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 & ")"