在Access VBA中更新SQL-从另一个表更新表值 [英] Update SQL in Access VBA -- Updating Table Values From Another Table

查看：122
本文介绍了在Access VBA中更新SQL-从另一个表更新表值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我对Access VBA和SQL编码还很陌生.到目前为止，通过使用互联网，我已经能够找到我所遇到问题的大多数答案.我目前正在尝试在MS Access(2013)VBA中编写一些代码，以在关闭特定表单时从同一数据库中的另一个表中更新一个表中的数据.

I'm fairly new to Access VBA and SQL coding. So far, I've been able to find most of the answers to issues I've had by using the internet. I'm currently trying to write some code in MS Access (2013) VBA that updates the data in one table from another table in the same database when a particular form closes.

到目前为止，我已经解决了几个错误，但是我在SQLReplace的"UPDATE"中遇到语法错误.可能还有其他我不知道的错误，但是我不确定.任何帮助/指导将不胜感激！

I've worked out several errors so far, but I'm stuck on a syntax error in the "UPDATE" for SQLReplace. There could be other errors that I don't know about yet, but I'm not sure. Any help/Guidance would be greatly appreciated!

谢谢！

Private Sub Form_Close()

Dim SQLMove As String
Dim SQLReplace As String

Dim CountyCaseType As String
Dim CaseNumber As String
Dim County As String
Dim FirstName As String
Dim MiddleName As String
Dim LastName As String
Dim Facility As String
Dim VOL As String
Dim Diagnosis As String
Dim AppearanceWaived As String
Dim Dismissed As String
Dim HearingResults As String
Dim Disposition As String
Dim DOB As String
Dim Minor As String
Dim Sex As String
Dim ClerkName As String
Dim Judge As String
Dim CourtDate As String


CountyCaseType = "Tables!tblTemp.CountyCaseType.Value"
CaseNumber = "Tables!tblTemp.CaseNumber.Value"
County = "Tables!tblTemp.County.Value"
FirstName = "Tables!tblTemp.FirstName.Value"
MiddleName = "Tables!tblTemp.MiddleName.Value"
LastName = "Tables!tblTemp.LastName.Value"
Facility = "Tables!tblTemp.Facility.Value"
VOL = "Tables!tblTemp.VOL.Value"
Diagnosis = "Tables!tblTemp.Diagnosis.Value"
AppearanceWaived = "Tables!tblTemp.AppearanceWaived.Value"
Dismissed = "Tables!tblTemp.Dismissed.Value"
HearingResults = "Tables!tblTemp.HearingResults.Value"
Disposition = "Tables!tblTemp.Disposition.Value"
DOB = "Tables!tblTemp.DOB.Value"
Minor = "Tables!tblTemp.Minor.Value"
Sex = "Tables!tblTemp.Sex.Value"
ClerkName = "Tables!tblTemp.Clerk.Value"
Judge = "Tables!tblTemp.Judge.Value"
CourtDate = "Tables!tblTemp.CourtDate.Value"

SQLMove = "INSERT INTO tblCalendar SELECT * FROM tblTemp"
SQLReplace = "UPDATE tblCalendar " & _
                     "SET tblCalendar.CountyCaseType.Value = CountyCaseType, " & _
                     "    tblCalendar.CaseNumber.Value = CaseNumber, " & _
                     "    tblCalendar.County.Value = County, " & _
                     "    tblCalendar.FirstName.Value = FirstName, " & _
                     "    tblCalendar.MiddleName.Value = MiddleName, " & _
                     "    tblCalendar.LastName.Value = LastName, " & _
                     "    tblCalendar.Facility.Value = Facility, " & _
                     "    tblCalendar.VOL.Value = VOL, " & _
                     "    tblCalendar.Diagnosis.Value = Diagnosis, " & _
                     "    tblCalendar.AppearanceWaived.Value = AppearanceWaived, " & _
                     "    tblCalendar.Dismissed.Value = Dismissed, " & _
                     "    tblCalendar.HearingResults.Value = HearingResults, " & _
                     "    tblCalendar.Disposition.Value = Disposition, " & _
                     "    tblCalendar.DOB.Value = DOB, " & _
                     "    tblCalendar.Minor.Value = Minor, " & _
                     "    tblCalendar.Sex.Value = Sex, " & _
                     "    tblCalendar.ClerkName.Value = Clerk, " & _
                     "    tblCalendar.Judge.Value = Judge, " & _
                     "FROM tblTemp " & _
                     "Where 'CourtDate = tblCalendar.CourtDate.Value'"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLMove)
DoCmd.RunSQL (SQLReplace)
DoCmd.SetWarnings True
End Sub

推荐答案

您的代码中存在一些潜在的错误:

There are several potential errors in your code:

  1. 您无需在属性的末尾添加.Value即可获取其实际值.

直接在Access中工作时，也不需要Tables!部分.那是处理记录集时使用的语法.例如，写tblTemp.CountyCaseType而不是Tables!tblTemp.CountyCaseType.Value

As you are working directly in Access, you to not need the Tables! part either. That is the syntax used when dealing with recordsets. For example, write tblTemp.CountyCaseType instead of Tables!tblTemp.CountyCaseType.Value

变量的值不在SQL字符串中.您必须使用 [&] 将它们连接到SQLReplace字符串.例如，写

The values of your variables are not in the SQL string. You have to concatenate them to the SQLReplace String using [&]. For example, write

SQLReplace = "UPDATE tblCalendar " & _
                 "SET tblCalendar.CountyCaseType = " & CountyCaseType & ", " & _
                 "    tblCalendar.CaseNumber = " & CaseNumber & ", " & _
                 ....

  • 正如@AlanHadsell指出的，请删除WHERE子句中的单引号.

  • As @AlanHadsell pointed out, remove the single quotes from the WHERE clause. 

    Where 'CourtDate = tblCalendar.CourtDate.Value'

    应该是

    WHERE CourtDate = tblCalendar.CourtDate

    但是正如我在 3)中所说的，CourTDate是一个String变量，因此需要将其连接起来.您最后的WHERE子句应为:

    But as I said in 3) CourTDate is a String variable, so it needs to be concatenated. Your final WHERE clause should be:

    "WHERE " & CourtDate & " = tblCalendar.CourtDate"

  • 您不需要SQLReplace字符串中的FROM tblTemp子句.

    EDIT :正如@Parfait指出的那样，tblTemp在SQLReplace语句的范围内不存在.您应该执行INNER JOIN来解决此问题:

    EDIT: As @Parfait pointed out, tblTemp does not exist in scope of the SQLReplace statement. You should do an INNER JOIN to fix that:

    UPDATE tblCalendar INNER JOIN tblTemp ON tblCalendar.CourtDate = tblTemp.CourtDate SET ...

    • 修复所有问题后，最终代码应如下所示:

    After fixing everything, your final code should look like:

    Private Sub Form_Close()

    Dim SQLMove As String
    Dim SQLReplace As String

    Dim CountyCaseType As String
    Dim CaseNumber As String
    Dim County As String
    Dim FirstName As String
    Dim MiddleName As String
    Dim LastName As String
    Dim Facility As String
    Dim VOL As String
    Dim Diagnosis As String
    Dim AppearanceWaived As String
    Dim Dismissed As String
    Dim HearingResults As String
    Dim Disposition As String
    Dim DOB As String
    Dim Minor As String
    Dim Sex As String
    Dim ClerkName As String
    Dim Judge As String
    Dim CourtDate As String


    CountyCaseType = "tblTemp.CountyCaseType"
    CaseNumber = "tblTemp.CaseNumber"
    County = "tblTemp.County"
    FirstName = "tblTemp.FirstName"
    MiddleName = "tblTemp.MiddleName"
    LastName = "tblTemp.LastName"
    Facility = "tblTemp.Facility"
    VOL = "tblTemp.VOL"
    Diagnosis = "tblTemp.Diagnosis"
    AppearanceWaived = "tblTemp.AppearanceWaived"
    Dismissed = "tblTemp.Dismissed"
    HearingResults = "tblTemp.HearingResults"
    Disposition = "tblTemp.Disposition"
    DOB = "tblTemp.DOB"
    Minor = "tblTemp.Minor"
    Sex = "tblTemp.Sex"
    ClerkName = "tblTemp.Clerk"
    Judge = "tblTemp.Judge"
    CourtDate = "tblTemp.CourtDate"

    SQLMove = "INSERT INTO tblCalendar SELECT * FROM tblTemp"
    SQLReplace = "UPDATE tblCalendar " & _
                 "INNER JOIN tblTemp ON tblCalendar.CourtDate = tblTemp.CourtDate " & _
                         "SET tblCalendar.CountyCaseType = " & CountyCaseType & ", " & _
                         "    tblCalendar.CaseNumber = " & CaseNumber & ", " & _
                         "    tblCalendar.County = " & County & ", " & _
                         "    tblCalendar.FirstName = " & FirstName & ", " & _
                         "    tblCalendar.MiddleName = " & MiddleName & ", " & _
                         "    tblCalendar.LastName = " & LastName & ", " & _
                         "    tblCalendar.Facility = " & Facility & ", " & _
                         "    tblCalendar.VOL = " & VOL & ", " & _
                         "    tblCalendar.Diagnosis = " & Diagnosis & ", " & _
                         "    tblCalendar.AppearanceWaived = " & AppearanceWaived & ", " & _
                         "    tblCalendar.Dismissed = " & Dismissed & ", " & _
                         "    tblCalendar.HearingResults = " & HearingResults & ", " & _
                         "    tblCalendar.Disposition = " & Disposition & ", " & _
                         "    tblCalendar.DOB = " & DOB & ", " & _
                         "    tblCalendar.Minor = " & Minor & ", " & _
                         "    tblCalendar.Sex = " & Sex & ", " & _
                         "    tblCalendar.ClerkName = " & Clerk & ", " & _
                         "    tblCalendar.Judge = " & Judge 
    DoCmd.SetWarnings False
    DoCmd.RunSQL (SQLMove)
    DoCmd.RunSQL (SQLReplace)
    DoCmd.SetWarnings True
End Sub

    最后，不必为要复制的tableTemp中的每个属性声明一个String变量，然后为它们分配一些值，您可以简单地省略声明并将这些属性分别放在SQL中.这样可以减少代码的长度，如下所示:

    To finish, instead of declaring a String variable for each attributes in tableTemp that you want to copy, and then assigning some values to them, you can simply omit the declarations and put the attributes dicrectly in the SQL. That will geatly reduce the length of your code as follow:

    Private Sub Form_Close()

    Dim SQLMove As String
    Dim SQLReplace As String

    SQLMove = "INSERT INTO tblCalendar SELECT * FROM tblTemp"
    SQLReplace = "UPDATE tblCalendar " & _
                 "INNER JOIN tblTemp ON tblCalendar.CourtDate = tblTemp.CourtDate " & _
                         "SET tblCalendar.CountyCaseType = tblTemp.CountyCaseType, " & _
                         "    tblCalendar.CaseNumber = tblTemp.CaseNumber, " & _
                         "    tblCalendar.County = tblTemp.County, " & _
                         "    tblCalendar.FirstName = tblTemp.FirstName, " & _
                         "    tblCalendar.MiddleName = tblTemp.MiddleName, " & _
                         "    tblCalendar.LastName = tblTemp.LastName, " & _
                         "    tblCalendar.Facility = tblTemp.Facility, " & _
                         "    tblCalendar.VOL = tblTemp.VOL, " & _
                         "    tblCalendar.Diagnosis = tblTemp.Diagnosis, " & _
                         "    tblCalendar.AppearanceWaived = tblTemp.AppearanceWaived, " & _
                         "    tblCalendar.Dismissed = tblTemp.Dismissed, " & _
                         "    tblCalendar.HearingResults = tblTemp.HearingResults, " & _
                         "    tblCalendar.Disposition = tblTemp.Disposition, " & _
                         "    tblCalendar.DOB = tblTemp.DOB, " & _
                         "    tblCalendar.Minor = tblTemp.Minor, " & _
                         "    tblCalendar.Sex = tblTemp.Sex, " & _
                         "    tblCalendar.ClerkName = tblTemp.ClerkName, " & _
                         "    tblCalendar.Judge = tblTemp.Judge"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (SQLMove)
    DoCmd.RunSQL (SQLReplace)
    DoCmd.SetWarnings True
End Sub

    这篇关于在Access VBA中更新SQL-从另一个表更新表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

    查看全文
    相关文章
    其他开发最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆