I'm using VBA 7 with MS Word to change field values in a SQLite database. I need to read every record in a table from one database, change it value and update some record in another table from second database. When I'm trying to update the second table, I get the error: 'only one SQL statement allowed'. I'd appreciate any help.
Sub IterateThruDB1andUpdateDB2()
Dim mCounter As Long
Dim mEntry As String
Dim mTranslation As String
Dim qExec As String
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "DSN=SomeDSN1;"
cnn1.Open
Set cnn2 = New ADODB.Connection
cnn2.ConnectionString = "DSN=SomeDSN2;"
cnn2.Open
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
mCounter = 0
If cnn1.State = adStateOpen Then
qExec = "SELECT * FROM someTable1"
Set rs1 = cnn1.Execute(qExec)
Do Until rs1.EOF
mWordId = rs1.Fields("_id")
mWord = rs1.Fields("word")
mTranslation = rs1.Fields("translation") & "Some text"
qExec = "UPDATE someTable2 SET translation='" & mTranslation & "' WHERE _id='" & mWordId & "'"
Set rs2 = cnn2.Execute(qExec)
If rs2.EOF Then
mCounterNotFound = mCounterNotFound + 1
resp = MsgBox("Couldn't update! [" & mWordId & "]")
If resp = vbCancel Or resp = vbNo Then
rs2.Close
Exit Do
End If
Else
mCounter = mCounter + 1
End If
rs2.Close
rs1.MoveNext
Loop
End If
rs1.Close
cnn1.Close
cnn2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set cnn1 = Nothing
Set cnn2 = Nothing
End Sub
If I change Update statement to Select, it works, so it actually allows two statements, but not Update or Insert. The second set is needed to check whether the record in the second table was successfully updated, so it needs to be closed in each cycle. The second set can be removed, but the error still occurs.
