Access中用无主键链接表更新本地表报错（错误3073）及解决

问题场景 #

我需要实现从ODBC链接表同步数据到本地BEL_PLZ表的功能：导入本地不存在的新条目，同时更新已有条目的内容。其中INSERT语句可以正常执行，但UPDATE语句抛出运行时错误3073。

初始实现代码 #

Sub UpdateBLPNR()
With CurrentDb
Set tdf = .CreateTableDef("ext_BEL_PLZ")
tdf.Connect = "ODBC;DSN=EasyProd PPS;DataDirectory=PATH;SERVER=NotTheServer;Compression= ;DefaultType=FoxPro;Rows=False;Language=OEM;AdvantageLocking=ON;Locking=Record;MemoBlockSize=64;MaxTableCloseCache=5;ServerTypes=6;TrimTrailingSpaces=False;EncryptionType=RC4;FIPS=False"
tdf.SourceTableName = "BEL_PLZ"
.TableDefs.Append tdf
.TableDefs.Refresh
End With
Dim SQLUpdate As String
Dim SQLInsert As String
SQLUpdate = "UPDATE BEL_PLZ " & _
"INNER JOIN ext_BEL_PLZ " & _
"ON(BEL_PLZ.NR = ext_BEL_PLZ.NR) " & _
"SET BEL_PLZ.BEZ = ext_BEL_PLZ.BEZ "
SQLInsert = "INSERT INTO BEL_PLZ (NR,BEZ) " & _
"SELECT NR,BEZ FROM ext_BEL_PLZ t " & _
"WHERE NOT EXISTS(SELECT 1 FROM BEL_PLZ s " & _
"WHERE t.NR = s.NR) "
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLUpdate)
DoCmd.RunSQL (SQLInsert)
DoCmd.SetWarnings True
DoCmd.DeleteObject acTable, "ext_BEL_PLZ"
End Sub

问题原因 #

直接使用INNER JOIN的UPDATE语句在Access中同时操作ODBC链接表和本地表时，会出现兼容性问题，导致运行时错误3073。

可行解决方案 #

感谢ComputerVersteher提供的思路，改用DAO Recordset遍历匹配记录，结合参数化查询逐个更新本地表条目，规避了跨表UPDATE的兼容性问题。修改后的代码如下：

Sub UpdateBLPNR()
'Define Variables
Dim SQLInsert As String
Dim qdf As DAO.QueryDef
'Create temporary table and update entries
With CurrentDb
Set tdf = .CreateTableDef("ext_BEL_PLZ")
tdf.Connect = "ODBC;DSN=EasyProd PPS;DataDirectory=PATH;SERVER=NotTheServer;Compression= ;DefaultType=FoxPro;Rows=False;Language=OEM;AdvantageLocking=ON;Locking=Record;MemoBlockSize=64;MaxTableCloseCache=5;ServerTypes=6;TrimTrailingSpaces=False;EncryptionType=RC4;FIPS=False"
tdf.SourceTableName = "BEL_PLZ"
.TableDefs.Append tdf
.TableDefs.Refresh
With .OpenRecordset("SELECT ext_BEL_PLZ.NR, ext_BEL_PLZ.BEZ " & _
"FROM ext_BEL_PLZ INNER JOIN BEL_PLZ ON BEL_PLZ.NR = ext_BEL_PLZ.NR", dbOpenSnapshot)
Set qdf = .Parent.CreateQueryDef("")
Do Until .EOF
qdf.sql = "PARAMETERS paraBEZ Text ( 255 ), paraNr Text ( 255 );" & _
"Update BEL_PLZ Set BEL_PLZ.BEZ = [paraBEZ] " & _
"Where BEL_PLZ.NR = [paraNr]"
qdf.Parameters("paraBez") = .Fields("BEZ").Value
qdf.Parameters("paraNr") = .Fields("NR").Value
qdf.Execute dbFailOnError
.MoveNext
Loop
End With
End With
'Run SQL Query (Insert)
SQLInsert = "INSERT INTO BEL_PLZ (NR,BEZ) " & _
"SELECT NR,BEZ FROM ext_BEL_PLZ t " & _
"WHERE NOT EXISTS(SELECT 1 FROM BEL_PLZ s " & _
"WHERE t.NR = s.NR) "
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLInsert)
DoCmd.SetWarnings True
'Drop temporary table
DoCmd.DeleteObject acTable, "ext_BEL_PLZ"
End Sub

核心修改说明 #

• 用dbOpenSnapshot打开包含匹配记录的结果集，逐条遍历需要更新的条目
• 创建参数化QueryDef，既避免了SQL注入风险，又解决了跨表UPDATE的兼容性问题
• 逐个设置参数并执行更新操作，确保每条记录的更新逻辑都能正确执行

内容的提问来源于stack exchange，提问作者Moritz