[QODBC-Desktop] Troubleshooting - How to get VBA to execute two statements at the same time
Posted by Jack - QODBC Support on 08 October 2015 08:42 AM

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


(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).