Azure SQL表 - > SQL Server视图 - >链接访问表：无法更新

So I have this cute daisy chain:

所以我有这个可爱的菊花链：

• A Table in an Azure SQL Database (tblAzure)
• Azure SQL数据库中的表（tblAzure）
• A View to tblAzure in a local SQL Server database (vwSQL)
• 本地SQL Server数据库中的tblAzure视图（vwSQL）
• A linked table in Access pointing to vwSQL (tblAccess)
• Access中的链接表指向vwSQL（tblAccess）

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

虽然部分单独工作，但我无法从Access更新Azure表。

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.

数据库之间的连接可以单独正常工作。我可以通过在vwSQL中插入值来更新tblAzure。我还可以通过链接的Access表更新SQL服务器中的表，甚至通过创建视图来链接Access并将Access链接到该视图。我还可以通过在Access中打开的vwSQL看到tblAzure中的值作为链接表。

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.

ODBC - 在链接表'tblAccess'上插入失败。

[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)

[Microsoft] [ODBC SQL Server驱动程序] [SQL Server] OLE DB提供程序“SQLNCLI11”用于链接服务器“azw”返回消息“参数不正确：。”。（＃7412）[Microsoft] [ODBC SQL Server驱动程序] [ SQL Server]链接服务器“azw”的OLE DB提供程序“SQLNCLI11”报告错误。提供者报告一个或多个参数无效。 （＃7399）[Microsoft] [ODBC SQL Server驱动程序] [SQL Server]无法执行操作，因为链接服务器“azw”的OLE DB提供程序“SQLNCLI11”无法启动分布式事务。 （＃7391）

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

有人见过这样的事吗？我尝试将链接属性/服务器选项/分发服务器更改为True，但它没有帮助。

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

基本思想是我需要一个在我们的数据库中完全同步的表，以及可由Access中的用户编辑的表。 （不要问，我继承了太复杂的系统......）

---

Test scripts

测试脚本

In Azure:

在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:

在SQL Server中：

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

（AZW是链接的Azure服务器的名称，HUFU是数据库）

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.

在Access中，我链接视图（我有许多其他链接的表和视图，它们都工作）。打开Access链接表（指向SQL服务器视图）它显示数据，但无法保存修改，显示上述错误。

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

我现在实际上有一个解决方法，但这件事让我烦恼，我很想知道这里有什么问题......

1 个解决方案

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

我不确定这将适用于您的情况，但上次我无法从MS Access更新SQL Server视图时，解决方案是确保代表该视图的Access链接表具有主键。

This is what I use to create the PK:

这就是我用来创建PK的方法：

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