在MS Access VBA中使用选择查询更新查询

Question

我想在所有ms访问表（我将导入）中添加4列，然后根据我选择的查询结果更新它们。但是，我的更新查询不起作用。任何人都可以请看看，让我知道了解决方案

感谢

Dim db As DAO.Database 
    Dim objtbl As DAO.TableDef 
    Dim sqlstr As String 
    Dim rs As DAO.Recordset 
    Dim sq2 As DAO.Recordset 
    Dim sq3 As DAO.Recordset 
    Dim sq4 As DAO.Recordset 
    Dim sq5 As DAO.Recordset 
    Set db = CurrentDb 
    On Error Resume Next 
    For Each objtbl In db.TableDefs 
    db.Execute "ALTER TABLE " & "[" & objtbl.Name & "]" & " ADD COLUMN  [Account] Text;" 
    db.Execute "ALTER TABLE " & "[" & objtbl.Name & "]" & " ADD COLUMN [Account_Serial] Text;" 
    db.Execute "ALTER TABLE " & "[" & objtbl.Name & "]" & " ADD COLUMN [Invoice_Date] Text;" 
    db.Execute "ALTER TABLE " & "[" & objtbl.Name & "]" & "ADD Column [LOB] Text;" 
    db.Execute "ALTER TABLE " & "[" & objtbl.Name & "]" & "ADD Column [LOB] Text;" 
    q = "Select [Field3] from " & "[" & objtbl.Name & "]" & " Where " & "[" & objtbl.Name & "].[Field1] = ""CUSTOMERRECORD"";" 
    Set rs = CurrentDb.OpenRecordset("Select [Field3] from " & "[" & objtbl.Name & "]" & " Where " & "[" & objtbl.Name & "].[Field1] = ""CUSTOMERRECORD"";") 
    Set sq2 = CurrentDb.OpenRecordset("Select Field4 from " & "[" & objtbl.Name & "]" & "Where" & "[" & objtbl.Name & "].[Field1] = ""CUSTOMERRECORD"";") 
    Set sq3 = CurrentDb.OpenRecordset("Select Field5 from" & "[" & objtbl.Name & "]" & "Where" & "[" & objtbl.Name & "].[Field1] = ""Customerrecord"";") 
    Set sq4 = CurrentDb.OpenRecordset("Select Field6 from" & "[" & objtbl.Name & "]" & "Where" & "[" & objtbl.Name & "].[Field1] = ""Customerrecord"";") 
    db.Execute 
    db.Execute "Update " & "[" & objtbl.Name & "] SET " & "[" & objtbl.Name & "].[Account] = " & rs!Field3 & ";" 
Next 

Answer 1

访问可能不会立即反映您对表进行，除非你刷新数据库窗口的变化。

尝试添加命令Application.RefreshDatabaseWindow在更改表格之后以及打开记录集之前。

Answer 2

请从您的代码中删除On Error Resume Next。然后修复您将获得的所有错误。例如。

• 失踪ADD前空间：& "ADD Column [LOB] Text;"
• 添加[LOB]后两次
• 缺少空间FROM：("Select Field5 from" & "[" &
• 您打开sq2..4但不与他们做任何事
• db.Execute无参数

定义变量sTable = "[" & objtbl.Name & "]"并使用该参数e会让你的代码更好的可读性。

如果字段3是一个文本字段，你可能需要

" .[Account] = '" & rs!Field3 & "';"