ADO SQL修改其他表上的数据？

我一直在尝试使用一个包含多个组合框的表单来更新某些记录，这些表单是从包含城市和部门的辅助表中填充的（需要更新的表只保存两个“侧”表中的ID .

由于我有一些困难时间直接插入数据，我决定使用ADO直接插入SQL .

到目前为止，我的VBA代码如下所示：

Private Sub btnClose_Click()
    Dim Msg, Style, Title, Ctxt, Help, Response, MyString

    Msg = "Want to save changes?"
    Style = vbYesNo + vbQuestion
    Tytle = "Confirm changes"
    Ctxt = 1000

    Response = MsgBox(Msg, Style, Tytle, Help, Ctxt)

    If Response = vbNo Then
        Cancel = True
    Else
        Call ManualUpdate
    End If
End Sub

而ManualUpdate子：

Private Sub ManualUpdate()
    Dim ccnDb As ADODB.Connection
    Set ccnDb = CurrentProject.Connection
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command

    Dim strSQL As String
    strSQL = "UPDATE personal SET personaApPaterno = " & "'" & Trim(Me.personaApPaterno.Value) & "'"
    strSQL = strSQL & ", personaApMaterno = " & "'" & Trim(Me.personaApMaterno.Value) & "'"
    strSQL = strSQL & ", personaNombre = " & "'" & Trim(Me.personaNombre.Value) & "'"
    strSQL = strSQL & ", personaCargo = " & "'" & Trim(Me.personaCargo.Value) & "'"
    strSQL = strSQL & ", departamentoId = " & Me.cmbDepto.Value
    strSQL = strSQL & ", ciudadId = " & Me.cmbCiudad.Value
    strSQL = strSQL & ", personaProfesion = " & "'" & Trim(Me.personaProfesion.Value) & "'"
    strSQL = strSQL & ", personaGerente = " & Me.personaGerente.Value
    strSQL = strSQL & ", personaExterno = " & Me.personaExterno.Value
    strSQL = strSQL & ", personaSexo = " & Me.ogSexo.Value
    strSQL = strSQL & " WHERE personaRUT = " & Me.personaRUT.Value

    If Me.Dirty Then
        Me.Dirty = False
    End If

    With cmd
        .CommandText = strSQL
        .ActiveConnection = ccnDb
        .Execute
    End With

    Set cmd = Nothing
    DoCmd.Close
End Sub

到现在为止还挺好...

正如你所看到的SQL对名为“personal”的表进行DIRECT升级，并且工作正常，让我发疯的唯一问题是，由于一些奇怪的原因，Access 2007正在更新辅助表并替换文本在“ciudadName”和“departamentoName”字段中有各自的ID . 仅仅为了一些额外的信息，我在ADO之前使用DAO .

这是DAO代码也......

Private Sub ManualUpdate()
    Dim dbDao As DAO.Database
    Dim rsDao As DAO.Recordset

    If Me.Dirty Then
        Me.Dirty = False
    End If

    Set dbDao = CurrentDb
    Set rsDao = dbDao.OpenRecordset("personal", dbOpenTable)

    rsDao.FindFirst ("personaRUT = " & Me.personaRUT.Value)
    rsDao.Edit

    rsDao![personaApPaterno] = Trim(Me.personaApPaterno.Value)
    rsDao![personaApMaterno] = Trim(Me.personaApMaterno.Value)
    rsDao![personaNombre] = Trim(Me.personaNombre.Value)
    rsDao![personaCargo] = Me.personaCargo.Value
    rsDao![departamentoId] = Me.cmbDepto.Value
    rsDao![comunaId] = Me.cmbComuna.Value
    rsDao![personaProfesion] = Me.personaProfesion.Value
    rsDao![personaGerente] = Me.personaGerente.Value
    rsDao![personaExterno] = Me.personaExterno.Value
    rsDao![personaSexo] = Me.ogSexo.Value

    rsDao.Update

    rsDao.Close
    dbDao.Close

    Set dbDao = Nothing
    Set rsDao = Nothing
    DoCmd.Close
End Sub

顺便说一句：DAO在同样的“更新”中不应该发生，这就是为什么我采用ADO方式，但根本没有运气 .

编辑：

决定添加一些我认为相关的额外信息，以便为我的问题获得正确的帮助 . 这个想法是为了展示不同的表如何相互关联 .

如果需要，我也可以上传自己的表格 .