查询不可更新 [英] Query not updateable

问题描述

我正在尝试使用BE SQL Server 2012 Express中的记录更新本地Access 2007表. 我在这里的步骤:

I am trying to update local Access 2007 table with records from BE SQL Server 2012 Express. My steps here:

1. 在SQL Server中，存储过程具有4个参数以获取所需的记录；

1. In SQL Server exists Stored Procedure with 4 parameters to get needed records;

在Access VBA中，有调用SP并进行临时查询的功能:

In Access VBA there is function to call SP and make temporary query:

Public Function UpdateLocalSQLTable(strTable As String, strSQL As
String, strSQL1 As String) As Boolean 
On Error GoTo Err_Handler
    Dim qdf As DAO.QueryDef
    Dim strQuery As String
    Dim conConnectString As String

strQuery = "qryTemp"
DoCmd.Close acTable, strTable

If IsQueryExists(strQuery) Then DoCmd.DeleteObject acQuery, strQuery

conConnectString = GetUserParams(NetConnDat)

Set qdf = CurrentDb.CreateQueryDef(strQuery)
With qdf
    .Connect = conConnectString
    .SQL = strSQL
    .Close 
End With

CurrentDb.Execute strSQL1
UpdateLocalSQLTable = True

qdf.Close

Exit_Handler:
    Set qdf = Nothing
    Exit Function 
Err_Handler:
    Call LogError(Err.Number, Err.Description, "UpdateLocalSQLTable", , True)
    Resume Exit_Handler 
End Function

以下是两个传递给子例程的SQL字符串:

Here are two SQL strings passed to subroutine:

strSQL=EXEC [dbo].[usp_TabelMakeTmpTable] @strEmp='0033111',@strMon='2014.12',@strDep='STR',@strPam='STR3'

strSQL1=UPDATE tbl_tmp_Tab_s INNER JOIN qryTemp ON tbl_tmp_Tab_s.EmplCodeID0 = qryTemp.EmplCodeID0 SET tbl_tmp_Tab_s.GraphHrs = [qryTemp]![GraphHrs];

我得到的查询"qryTemp"创建得很好，但是在UPDATE语句中却收到错误3073.操作必须使用可更新的查询".

I get query "qryTemp" created well but on UPDATE statement I get error 3073. "Operation must use updateable query".

怎么了?

推荐答案

Access始终将包含联接的直通查询的UPDATE视为只读.即使UPDATE不尝试更改传递中的值，这一点也适用.

Access always treats an UPDATE which includes a joined pass-through query as read-only. And that holds true even when the UPDATE does not attempt to alter values in the pass-through.

正如您在评论中提到的那样，您可以将传递结果集存储在Access表中.在UPDATE中代替该传递连接该表应该可行.但是，您还提到这似乎有点肮脏.那里没有争论. :-)

As you mentioned in a comment, you could store the pass-through result set in an Access table. Joining that table in place of the pass-through in the UPDATE should work. However, you also mentioned that seems a bit dirty. No argument there. :-)

因此，也许您希望使用DLookup来获取UPDATE的 qryTemp.GraphHrs 值.这应该可以，但是我不知道执行速度是否可以接受...

So perhaps you would prefer to use DLookup to fetch qryTemp.GraphHrs values for the UPDATE. This one should work, but I don't know whether the execution speed will be acceptable ...

UPDATE tbl_tmp_Tab_s
SET tbl_tmp_Tab_s.GraphHrs =
    DLookup(
        "GraphHrs",
        "qryTemp",
        "EmplCodeID0=" & EmplCodeID0
        );

如果 EmplCodeID0 的数据类型是文本而不是数字，请在其值周围加上引号...

If the datatype of EmplCodeID0 is text rather than numeric, include quotes around its value ...

        "EmplCodeID0='" & EmplCodeID0 & "'"

这篇关于查询不可更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！