Knowledgebase: Tutorials
[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 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"

Solutions:

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

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(refnumber,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).