Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    4

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

    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.
    Attached Files Attached Files

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,633

    Re: VBA 'only one SQL statement allowed' error, when writing from one database to ano

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    4

    Re: VBA 'only one SQL statement allowed' error, when writing from one database to ano

    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.

  4. #4
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,633

    Re: VBA 'only one SQL statement allowed' error, when writing from one database to ano

    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:
    Code:
    UPDATE 
          t2
    SET 
         t2.translation= t1.translation
    FROM 
          someTable1 t1 
         INNER JOIN someTable2 t2 
         ON t1.id = t2.id and t1.WordId = t2.WordId;
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    4

    Re: VBA 'only one SQL statement allowed' error, when writing from one database to ano

    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width