VBA 'only one SQL statement allowed' error, when writing from one database to another

[Amaltus]

Hi! 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 in one database and write it to another table in another database. When I'm trying to get the second record set I get the error: 'only one SQL statement allowed'. I attached the code.
I'd appreciate any help.

[sapator]

This does not really belong to Database Development more like to VBA development but anyhow from what I see you might try to keep a list with you select data in memory, close the first connection when loop finish and the open a new second connection to update the data. I'm not sure how happy VBA is with 2 concurrent connections open but I haven't used it in ages and also something strikes bad to me in the code but, VBA so I'm not sure.

[Amaltus]

You probably meant record sets, because the macro opens both connections to two different databases at the beginning and closes them only at the end. The macro reads the entire table into the first record set. The second record 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 record set actually can be removed, but the error still occurs.

[sapator]

Ye I don't know what to tell you. either ask to be moved to VBA forum or open a new thread there or try doing a full update on both tables.
Something like:

UPDATE 
      t2
SET 
     t2.translation= t1.translation
FROM 
      someTable1 t1 
     INNER JOIN someTable2 t2 
     ON t1.id = t2.id and t1.WordId = t2.WordId;

[Amaltus]

I got help from some other place:

"There were two problems in the code:

As Alex suggested, I replaced
"UPDATE someTable2 SET translation='" & mTranslation & "' WHERE _id='" & mWordId & "'"
with
"UPDATE someTable2 SET translation='" & Replace(mTranslation, "'", "''") & "' WHERE _id='" & mWordId & "'" and the first problem was gone.
As Tim noted, executing the update query returns a closed recordset, so I removed it and it allowed to finish the job."