使用VBA向Access联结表插入外键时testSQL3执行失败是什么原因

问题根因 #

你写的testSQL3存在逻辑错误，核心问题如下：

• 你的查询逻辑内联结了目标插入表tblRejectionReason，而你刚插入的不合格品、原因记录还没有在这张联结表中存在对应关联关系，内联结的查询结果为空，自然无法插入任何数据。
• 你不需要通过多表关联查询的方式获取刚插入的两张表主键，Access提供了@@IDENTITY变量可以直接获取当前会话最近插入的自增主键值，效率和安全性都远高于查表取值。
• 额外发现：testSQL2中写入的字段名reasondescription和你给出的tblReason表结构的ReasonDesc字段不匹配，且Display until字段名带空格，SQL中需要用方括号包裹才能识别。

修正后的代码 #

Dim testSQL1 As String
Dim testSQL2 As String
Dim testSQL3 As String
Dim newRejectionID As Long
Dim newReasonID As Long

' 插入不合格品记录
testSQL1 = "INSERT INTO tblRejection (period, tescoyear, tescoweek, rejectiondate, packsize, TPNB, TPND, batchcode, productID) VALUES ('x','xxxx','xx','xxxxx','xxxxx','xxxxxxx','xxxxxx','xxxxxx',1);"
Debug.Print testSQL1
DoCmd.RunSQL testSQL1
' 读取刚生成的不合格品自增主键
newRejectionID = CurrentDb.OpenRecordset("SELECT @@IDENTITY")(0)
Me.Refresh

' 插入不合格原因记录
testSQL2 = "INSERT INTO tblReason (ReasonDesc, quantity, casesinspected, inspectedpacks, affectedpacks, percentageaffected, [Display until]) VALUES ('zzzzzzz',20,100,50,20,'xxxx','xxxxxx');"
Debug.Print testSQL2
DoCmd.RunSQL testSQL2
' 读取刚生成的原因自增主键
newReasonID = CurrentDb.OpenRecordset("SELECT @@IDENTITY")(0)
Me.Refresh

' 直接插入关联关系到联结表
testSQL3 = "INSERT INTO tblRejectionReason (rejectionid, reasonid) VALUES (" & newRejectionID & ", " & newReasonID & ");"
Debug.Print testSQL3
DoCmd.RunSQL testSQL3
Me.Refresh

补充说明 #

如果你的场景确实需要用查询方式插入关联关系，也不能内联结待插入的tblRejectionReason表，可参考如下写法（仅适合单用户场景，多用户并发下可能出现关联错误，优先推荐上面的@@IDENTITY方案）：

INSERT INTO tblRejectionReason (rejectionid, reasonid)
SELECT MAX(tblRejection.RejectionID), MAX(tblReason.ReasonID)
FROM tblRejection, tblReason

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