Query not updateable

问题:

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

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

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

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];

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

What's wrong with it?

回答1:

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.

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

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

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

        "EmplCodeID0='" & EmplCodeID0 & "'"