Could not start transaction; too many nested transaction

[JDog]

Hi,

I have written a whole lot of VBA code that extracts data from bloomberg and inserts it into my database that I have created in Access. The problem is that when I try to run my code I always get this error message:

Could not start transaction; too many nested transaction

and then access crashes.

The only way I seem to be able to run the code fine is when I insert breakpoints and step through the code. This generally enables it to run fine. Any ideas on why this is happening?

cheers,
Jeremy

 

[missinglinq]

First off, have absolutely no experience using transactions in Access, and in 8 years of trolling this, and half a dozen other Access forums, daily, have only seen them mentioned half-a-dozen times or so! But your statement

Hi,

...The only way I seem to be able to run the code fine is when I insert breakpoints and step through the code. This generally enables it to run fine...

is usually indicative of a timing problem, wherein Access/Windows cannot process data fast enough to keep from 'stumbling' on itself, if you will.

Access is asynchronous, which is to say, if given a series of Commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause the aforementioned timing problems.

We see it in situations where someone is trying to sequentially run a large number of Reports, or likewise run a series of complex Queries, where each Query (excepting the initial one) is based on the previously run Query. In a situation like

DoCmd.OpenQuery "QueryA"
DoCmd.OpenQuery "QueryB"
DoCmd.OpenQuery "QueryC"

the problem can be resolved using the DoEvents Command.

DoCmd.OpenQuery "QueryA"
DoEvents
DoCmd.OpenQuery "QueryB"
DoEvents
DoCmd.OpenQuery "QueryC"

DoEvents returns control to Windows, allowing QueryA to complete running before starting to run QueryB. It then allows QueryB to finish running before starting QueryC. You might want to try breaking up your code by using it at strategic points. Having no experience with transactions, I can't really tell you where to insert it, but perhaps inserting it between each transaction might do it. So play around with it and see what happens.

Linq ;0)>

 

[JDog]

Thanks for your insights.

What I don't understand is where I am using a "transaction". What is defined as a "transaction"?

My code executes a whole bunch of statements as follows:

strSQL = "UPDATE tblSecurities SET " & ConvertBBergFieldName(field.Name) & " = " & field.Value & " WHERE fldsec = '" & strSec & "' AND fldExch = '" & strExch & "'"
Debug.Print strSQL
CurrentDb.Execute strSQL

Unfortunately I have tried inserting random "doevents" but it still causes access to crash. So you think the key is finding the right place to insert these statements so the code doesn't crash?

 

[missinglinq]

Sorry, reading your original post I assumed that you were intentionally using transactions!

This post appears to be addressing a similar problem and may help:

http://bytes.com/topic/access/answers/880614-recordset-error-too-many-transactions

Be sure to notice that in the line

The Microsoft support site webpage is here and a workaround is here

the 'heres' are actually links; I say this because on my monitor it's almost impossible to see the difference in colors designating that they are links, and it's important to the post.

Linq ;0)>