我有许多工作簿，它们链接到Access数据库并通过查询绘制数据。我最近将数据库拆分为fe/be，并将前端分发给不同的用户。
某些用户，但不是所有用户无法刷新某些工作簿中的数据。他们收到错误消息："查询未运行或无法打开表。请检查数据库服务器或与数据库管理员联系。确保外部数据库可用且未被移动或重新组织，然后重试该操作"。
我试着将Excel本地机器上的连接属性中的连接文件更改为他们的个人前端，也更改为我的副本，但收到了相同的错误消息。
以下是一个有问题的查询中的sql，以帮助识别问题：
SELECT tblFitExtractMain.[Issue ID], tblFitExtractMain.[Date Entered], IIf([DateClosed] Is Not Null,Year([DateClosed]),IIf([DateMovedOutofShip] Is Not Null,Year([DateMovedOutofShip]),IIf([DateMovedIntoQualClosure] Is Not Null,Year([DateMovedIntoQualClosure]),Year([Date Entered])))) AS [Year], IIf([DateClosed] Is Not Null,Month([DateClosed]),IIf([DateMovedOutofShip] Is Not Null,Month([DateMovedOutofShip]),IIf([DateMovedIntoQualClosure] Is Not Null,Month([DateMovedIntoQualClosure]),Month([Date Entered])))) AS [Month], tblFitExtractMain.Status, tblProductMatrix.product_variant, IIf([product_variant]="G4" Or [product_variant]="G3" Or [product_variant]="CLM","Pulsed","Systems") AS [Product line], IIf([DateArrivedatSPIUK] Is Not Null And [DateMovedOutof4SPIIncoming] Is Not Null,DateDiff("d",[DateArrivedatSPIUK],[DateMovedOutof4SPIIncoming]),IIf([DateArrivedAtSPI] Is Not Null And [DateMovedOutof4SPIIncoming] Is Not Null,DateDiff("d",[DateArrivedatSPIUK],[DateMovedOutof4SPIIncoming]),IIf([DateMovedOutofTransit] Is Not Null And [DateMovedOutof4SPIIncoming] Is Not Null,DateDiff("d",[DateMovedOutofTransit],[DateMovedOutof4SPIIncoming]),Null))) AS [Section 4 pre], IIf([Section 4 pre]=0,1,Abs([Section 4 pre])) AS [Section 4 calc], IIf([DateMovedIntoPreliminary] Is Not Null And [DateMovedOutOfPreliminary] Is Not Null,DateDiff("d",[DateMovedIntoPreliminary],[DateMovedOutOfPreliminary]),IIf([DateMovedOutof4SPIIncoming] Is Not Null And [DateMovedOutOfPreliminary] Is Not Null,DateDiff("d",[DateMovedOutof4SPIIncoming],[DateMovedOutOfPreliminary]),Null)) AS [Section 5 pre], IIf([Section 5 pre]=0,1,Abs([Section 5 pre])) AS [Section 5 calc], IIf([DateMovedIntoEngInv] Is Not Null And [DateMovedOutOfEngInv] Is Not Null,DateDiff("d",[DateMovedIntoEngInv],[DateMovedOutOfEngInv]),IIf([DateMovedOutOfPreliminary] Is Not Null And [DateMovedOutOfEngInv] Is Not Null,DateDiff("d",[DateMovedOutOfPreliminary],[DateMovedOutOfEngInv]),Null)) AS [Section 6a pre], IIf([Section 6a pre]=0,1,Abs([Section 6a pre])) AS [Section 6a calc], IIf([DateMovedInto6bCommRevReplace] Is Not Null And [DateMovedOutof6bCommRev] Is Not Null,DateDiff("d",[DateMovedInto6bCommRevReplace],[DateMovedOutof6bCommRev]),IIf([DateMovedOutOfEngInv] Is Not Null And [DateMovedOutof6bCommRev] Is Not Null,DateDiff("d",[DateMovedOutOfEngInv],[DateMovedOutof6bCommRev]),Null)) AS [Section 6b pre], IIf([Section 6b pre]=0,1,Abs([Section 6b pre])) AS [Section 6b calc], IIf([DateMovedOutof6bCommRev] Is Not Null And [DateMovedOutof7Rep] Is Not Null,DateDiff("d",[DateMovedOutof6bCommRev],[DateMovedOutof7Rep]),IIf([DateMovedOutOfEngInv] Is Not Null And [DateMovedOutof7Rep] Is Not Null,DateDiff("d",[DateMovedOutOfEngInv],[DateMovedOutof7Rep]),Null)) AS [Section 7 pre], IIf([Section 7 pre]=0,1,IIf([Section 7 pre]<0,Null,[Section 7 pre])) AS [Section 7 calc], IIf([DateMovedIntoShip] Is Not Null And [DateMovedOutofShip] Is Not Null,DateDiff("d",[DateMovedIntoShip],[DateMovedOutofShip]),IIf([DateMovedOutof7Rep] Is Not Null And [DateMovedOutofShip] Is Not Null,DateDiff("d",[DateMovedOutof7Rep],[DateMovedOutofShip]),IIf([DateMovedOutof6bCommRev] Is Not Null And [DateMovedOutofShip] Is Not Null,DateDiff("d",[DateMovedOutof6bCommRev],[DateMovedOutofShip]),Null))) AS [Section 8 pre], IIf([Section 8 pre]=0,1,[Section 8 pre]) AS [Section 8 calc], IIf([DateClosed] Is Not Null,[DateClosed],IIf([DateMovedOutofShip] Is Not Null,[DateMovedOutofShip],IIf([DateMovedIntoQualClosure] Is Not Null,[DateMovedIntoQualClosure],[Date Entered]))) AS [Date closed], tblProductMatrix.power, tblFitExtractMain.[Warranty Status]
FROM tblFitExtractMain INNER JOIN tblProductMatrix ON tblFitExtractMain.[Serial Number(1)] = tblProductMatrix.serial_number
WHERE (((Left([Status],1))>="9"));
非常感谢
我也遇到过类似的问题，但在Excel中使用了MS SQL后端。将临时新工作表添加到他们的工作簿中，并创建一些快速查询，将更新后的连接中的数据拉入临时工作表。如果数据没有通过此方法，请尝试添加具有相同属性/字符串的新连接，并查看数据是否通过临时工作表。这将至少隔离问题，希望您不必手动将每个受影响的用户更新到新的连接对象。
如果你有很多用户，这可能会很麻烦。在我的情况下，用户没有个性化的工作簿，所以我可以让员工简单地开始使用新的XLS文件。