Troubleshooting - How to get VBA to execute two statements at the same time
We are using a Visual Basic app which works with an SQL Server database to write data to QB through QODBC. We are executing two SQL statements as described in your help. We are getting an error saying either:
1) The transactions are not in balance - meaning it seems that both transactions are not being executed at the same time or
2) If we try to combine the two text strings into one SQL server statement that "missing semicolon at end of SQL statement"
You are getting transaction not in balance error because only one statement is executed. You should execute both statements one by one.
You cannot run both the statement at the same time, you have to run it one after the other.
Also, the connection should not be closed between the executions of two queries. If connection closed, then you will face the error.
Please refer below mentioned sample VBA code for executing two statement one after the other:
Public Sub exampleInsert()
Const adOpenStatic = 3
Const adLockOptimistic = 3
DoCmd.RunSQL " INSERT INTO JournalEntryCreditLine(refnumber,journalcreditlineaccountreffullname,JournalCreditLineAmount,JournalCreditLineMemo,FQSaveToCache) Values ('1234','operating1',555.00,'test memo transfer cred',1)”
DoCmd.RunSQL " INSERT INTO JournalEntryDebitLine(refnumber,JournalDebitLineAccountReffullname,JournalDebitLineAmount,JournalDebitLineMemo,FQSaveToCache) VALUES ('1234','operating2',555.00,'Test Memo transfer deb',0)"
sMsg = sMsg & "Record Added!!!"
Set oRecordset = Nothing
Set oConnection = Nothing