同数据库下如何根据Inward表入库数量更新Netstock表对应商品库存

报错核心原因 #

• UPDATE语句语法错误：
  • 赋值给P_Qty字段的子查询返回Item/Category/Sum(P_Qty)3个字段，单字段无法接收多列返回值
  • 子查询未和外层Netstock表建立关联，WHERE条件中引用的Inward表字段无法被外层语句识别
• 用法错误：Recordset.Open仅用于执行有结果集返回的查询，UPDATE这类无结果的动作查询应该使用Connection.Execute方法执行
• 逻辑错误：连续两次对同一个Recordset对象调用Open方法，第一次UPDATE执行报错会直接中断流程，根本不会执行后续的Netstock查询。

修正后可直接运行的代码 #

Dim con As New ADODB.Connection 
Dim rs As New ADODB.Recordset 

' 初始化数据库连接
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database.mdb;" 
rs.CursorLocation = adUseClient

' 先补全Netstock中不存在的新商品档案
con.Execute "INSERT INTO Netstock (Item, Category, Brand, P_Qty) " & _
            "SELECT Item, Category, Brand, 0 " & _
            "FROM ( " & _
            "  SELECT DISTINCT Item, Category, Brand FROM Inward " & _
            ") AS AllInwardItems " & _
            "LEFT JOIN Netstock  " & _
            "ON AllInwardItems.Item = Netstock.Item  " & _
            "AND AllInwardItems.Category = Netstock.Category  " & _
            "AND AllInwardItems.Brand = Netstock.Brand " & _
            "WHERE Netstock.Item IS NULL;"

' 执行库存同步更新：按商品、分类、品牌维度汇总入库数量，更新对应库存值
con.Execute "UPDATE Netstock " & _
            "INNER JOIN ( " & _
            "    SELECT Item, Category, Brand, Sum(P_Qty) AS TotalInQty " & _
            "    FROM Inward " & _
            "    GROUP BY Item, Category, Brand " & _
            ") AS InwardSum " & _
            "ON Netstock.Item = InwardSum.Item " & _
            "AND Netstock.Category = InwardSum.Category " & _
            "AND Netstock.Brand = InwardSum.Brand " & _
            "SET Netstock.P_Qty = InwardSum.TotalInQty;"

' 查询更新后的库存数据绑定到DataGrid
rs.Open "SELECT * FROM Netstock;", con, adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = rs

' 释放资源
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing

Inward表变更自动同步实现 #

要满足Inward表新增、编辑、删除操作后自动同步Netstock的需求，两种方案可选：

• 代码层同步：在VB6项目中所有对Inward表执行增/删/改操作的逻辑后面，统一调用上面写的全量汇总更新SQL，适合数据量不大的小型库存项目，实现简单无额外配置
• 数据库层同步：在Access数据库中为Inward表配置插入后、更新后、删除后的数据宏，直接在数据库端完成库存值的增减调整，不需要在业务代码里重复写同步逻辑，数据一致性更高。

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