Access VBA中SQL复制表数据报错求助：SQL2出现运行时错误3075

首先，你的运行时错误3075完全是SQL语法硬伤导致的——*StockNo这种写法是无效的：*代表选择所有列，你把它和列名StockNo拼在一起，Access数据库根本无法识别这个表达式，自然会提示"缺少操作符"。

错误根源 #

你大概率是想表达两种需求之一，但写法出错了：

• 若要选择所有列：应该单独用*，比如SELECT * FROM tblChemicalStock1
• 若要选择特定列StockNo：直接写StockNo即可，比如SELECT StockNo, ChemicalName FROM tblChemicalStock1
  绝对不能把*和列名组合成*StockNo，这是语法错误。

针对你的场景的正确SQL写法 #

假设你想从tblChemicalStock1复制数据到tblChemicalStock，分三种常见场景给你修正后的代码：

场景1：插入新记录（避免重复）

如果要把tblChemicalStock1中tblChemicalStock没有的记录插进去，用INSERT INTO：

Private Sub DataUpdate()
    Dim SQL1 As String
    Dim SQL2 As String
    
    ' 你的原有SQL1（正常运行的更新语句）
    SQL1 = "UPDATE tblChemicalStock " & _
           "INNER JOIN tblChemicalStock1 " & _
           "ON (tblChemicalStock.ChemicalName = tblChemicalStock1.ChemicalName) " & _
           "SET tblChemicalStock.Stock1 = tblChemicalStock1.Stock1, " & _
           "tblChemicalStock.Stock2 = tblChemicalStock1.Stock2"
    
    ' 修正后的SQL2：插入StockNo等字段的新数据
    SQL2 = "INSERT INTO tblChemicalStock (ChemicalName, StockNo, Stock1, Stock2) " & _
           "SELECT ChemicalName, StockNo, Stock1, Stock2 FROM tblChemicalStock1 " & _
           "WHERE NOT EXISTS (" & _
               "SELECT 1 FROM tblChemicalStock " & _
               "WHERE tblChemicalStock.ChemicalName = tblChemicalStock1.ChemicalName" & _
           ")"
    
    ' 执行SQL，dbFailOnError会在出错时抛出详细信息，方便调试
    On Error GoTo ErrorHandler
    CurrentDb.Execute SQL1, dbFailOnError
    CurrentDb.Execute SQL2, dbFailOnError
    MsgBox "数据更新完成！", vbInformation
    Exit Sub
    
ErrorHandler:
    MsgBox "错误：" & Err.Description, vbCritical
End Sub

场景2：更新已有记录的StockNo

如果要把tblChemicalStock1中的StockNo同步到tblChemicalStock的对应记录（和SQL1的更新逻辑一致），用UPDATE：

SQL2 = "UPDATE tblChemicalStock " & _
       "INNER JOIN tblChemicalStock1 " & _
       "ON tblChemicalStock.ChemicalName = tblChemicalStock1.ChemicalName " & _
       "SET tblChemicalStock.StockNo = tblChemicalStock1.StockNo"

场景3：创建新表并复制数据

如果想生成一个新表来存储复制的数据，用SELECT INTO：

SQL2 = "SELECT ChemicalName, StockNo, Stock1, Stock2 INTO tblNewChemicalStock " & _
       "FROM tblChemicalStock1"

额外调试技巧 #

• 执行SQL前加Debug.Print SQL2，打开VBA立即窗口（Ctrl+G）查看完整SQL字符串，直接复制到Access查询设计里运行，能更快定位语法问题。
• 如果字段名包含空格或特殊字符（比如Stock No），一定要用方括号包裹：[Stock No]。

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