Transaction problem new post 2

[Rotsey]

I removed the transaction and the code ran fine.

So then I tried this. Added this global variable

Set gobjConn = gobjWSodbc.OpenConnection(mConnectName, dbDriverNoPrompt,
False, _
"ODBC;DSN=" & rs!DSN & ";SERVER=" & rs!Server &
";WSID=LESTER34;DATABASE=" & rs!Database &
";TranslationName=Yes;QueryLogFile=Yes")

Then changed this line to

Set rs2 = gobjDBodbc.OpenRecordset(sql, dbOpenSnapshot)

to
Set rs2 = gobjConn .OpenRecordset(sql, dbOpenSnapshot)

And all works fine.


rotsey





I could be wrong about the source of the problem. As a test to attempt to
narrow it down a bit, could you try temporarily running the code without
using a transaction, by commenting out the lines indicated below. The result
should at least confirm whether the problem is related to the use of
transactions or not.

'temporarily comment out this line
'objws.Begintrans

For cntr = 1 to NmberOfInvoiceToCreate

invno = CreateInvoiceNumber(CustNoDetails(cntr))

SQL = "SELECT * FROM Invoices WHERE invNumber = " & invno
Set rs2 = gobjDBodbc.OpenRecordset(sql, dbOpenSnapshot)


if rs2.bof then
CreateInvoice(invno)
Endif

Next

'temporarily comment out this line
'objws.CommiTrans

 

[Tom Wickerath]

Hi Rotsey,

I'd like to suggest that you post your reply back to the original thread
that Brendan was helping you with, to close it out.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________