Troubleshooting - How to get VBA to execute two statements at the same time
Problem Description:
We use a Visual Basic app 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 the end of SQL statement."
Solutions:
You are getting a transaction not in balance error because only one statement is executed. You should execute both statements one by one.
You cannot run both statements at the same time. You have to run them one after the other.
Also, the connection should not be closed between the executions of two queries. If the connection is closed, then you will face the error.
Please refer to the below-mentioned sample VBA code for executing two statements one after the other:
Public Sub exampleInsert()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
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(ref number,JournalDebitLineAccountReffullname, JournalDebitLineAmount, JournalDebitLineMemo, FQSaveToCache) VALUES ('1234', 'operating2',555.00, 'Test Memo transfer deb,' 0)."
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg
Set oRecordset = Nothing
Set oConnection = Nothing
End Sub
|