By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,484 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

Recordset error "too many transactions"

reginaldmerritt
100+
P: 201
When opeing and closing a DAO recordset a few times i get the following error message:

Run-time error '3003';
Could not start transaction; too many transactions already nested.
I've been google and forum investigating all day. The only thing i have found is information on Microsofts support site regarding a bug but it seems to only mention ADO recordsets.

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

However when i use this workaround, which states to close the Recordsets's active connections by using ActiveConnection = Nothing I get the following error:

Run-time error '438';
Object doesn't support this property or methord
A simplised version of the code i'm using is:
Expand|Select|Wrap|Line Numbers
  1. Dim ChargeRateRecordSet As DAO.Recordset
  2. Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("Select * FROM ChargeRates ")
  3. ChargeRatesRecordSet.ActiveConnection = Nothing
  4. ChargeRatesRecordSet.Close
  5.  
Is the problem i'm having with "too many transactions already nested" a bug in ms access? If so is the workaround i'm using correct or is there a different syntax for DAO recordsets?

Any help would be great, thanks.
Jan 28 '10 #1
Share this Question
Share on Google+
18 Replies


nico5038
Expert 2.5K+
P: 3,072
Looks like Access creates a loop because of the SELECT statement.
Try just:
Expand|Select|Wrap|Line Numbers
  1. Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("ChargeRates")
  2.  
as the table will be seen as a single recordset.

Nic;o)
Jan 28 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
Well when your using a DAO recordset like that you should not set the connection. You allready have a source for your recordset when you specified CurrentDB.

You should however remember when your done with your recordset to set the recordset=nothing.
Expand|Select|Wrap|Line Numbers
  1. Set ChargeRatesRecordSet=Nothing
However nothing of the code shown here displays any TRANSACTIONS.

Remember that access VBA does not always point at the correct line of code causing the error. Do you use transactions in any place, and do you remember to commit them?
Jan 28 '10 #3

reginaldmerritt
100+
P: 201
Thanks for the replies,

The code above was a cut down version. My actual select statment is:

Expand|Select|Wrap|Line Numbers
  1. Set ChargeRatesRecordSet = CurrentDb.OpenRecordset
  2. ("Select Top 1 * 
  3. FROM ChargeRates 
  4. WHERE [DateAsOf] <= #" & DateFromForm & "# 
  5. ORDER BY [DateAsOf] DESC;")
  6.  
thanks for the post nico5038. Is there anything wrong the code above ?

Using Set ChargeRatesRecordSet=Nothing works as the correct syntax, however, i still have the same problem with too many transactions already nested after opening and closing the recordset a few times.

The line of code pointed to when the error occurs is the code above. But as you so rightly say it could be that the error is actually caused somewhere else.

I do have tranasactions in place, i think? transactions are where the recordset is accesed, is that correct? In which case yes i have a few but they are all read the recordset and don't edit or update the recordset in anyway.

An example of some of the recordsets i use is below:

Expand|Select|Wrap|Line Numbers
  1. If Me.Controls("AppFrmVolunteer" & LineNumber) = True Then
  2. Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureCOST")
  3. Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureADMIN")
  4. Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureVAT")
  5. Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureTotal")
  6.  
TheSmileyOne what do you mean by do you remember to commit them?
Jan 29 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
A transaction is something you use to control multi table updates. Since you can only update one table at a time, you may run into situations where you need transactoin. Take this eksample. You Take money from Person A in 1 table, and give them to Person B in another table. What if the computer/connection fails AFTER taking the money but BEFORE giving the money?

To prevent this people use transactions. In very simple terms, you start it with BeginTrans and once you succesfully reach the end of your code, you do a CommitTrans. If code fails/errors you can do a RollBack.

Have you used any of those keywords such as BeginTrans? If you haven't I don't think your using transactions. If that is the case, I would need to see more code, to realise where the error is occuring.
Jan 29 '10 #5

reginaldmerritt
100+
P: 201
Thanks for the clear explination. In that case, no i'm not using transactions.

Here is the full sub routine, there is another 2 subrountines that use recordssets but the error only seems point to this one.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CalculateInovice()
  2. Dim DateFromForm
  3. Dim Counter As Integer
  4.  
  5. 'get correct to date values from chargerates
  6. If Not IsNull(Me.Controls("AppFrmNum" & LineNumber)) Then
  7.  
  8.     DateFromForm = Format(Me.Controls("AppFrmDate" & LineNumber), "m/d/y")
  9.  
  10.     Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("Select Top 1 * FROM ChargeRates WHERE [DateAsOf] <= #" & DateFromForm & "# ORDER BY [DateAsOf] DESC;")
  11.  
  12.     ' if ISA Reg Only
  13.     If Me.Controls("AppFrmISAReg" & LineNumber) = True And Me.Controls("AppFrmStandard" & LineNumber) = False And Me.Controls("AppFrmEnhanced" & LineNumber) = False Then
  14.         Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyCOST")
  15.         Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyADMIN")
  16.         Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyVAT")
  17.         Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyTotal")
  18.     End If
  19.     ' If ISA Reg and CRB (Enhanced)
  20.     If (Me.Controls("AppFrmISAReg" & LineNumber) = True And Me.Controls("AppFrmStandard" & LineNumber) = True) Or (Me.Controls("AppFrmISAReg" & LineNumber) = True And Me.Controls("AppFrmEnhanced" & LineNumber) = True) Then
  21.         Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("EnhancedISARegCOST")
  22.         Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("EnhancedISARegADMIN")
  23.         Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("EnhancedISARegVAT")
  24.         Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("EnhancedISARegTotal")
  25.     End If
  26.     'if Standard CRB Only
  27.     If Me.Controls("AppFrmISAReg" & LineNumber) = False And Me.Controls("AppFrmStandard" & LineNumber) = True Then
  28.         Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("StandardDisclosureCOST")
  29.         Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("StandardDisclosureADMIN")
  30.         Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("StandardDisclosureVAT")
  31.         Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("StandardDisclosureTotal")
  32.     End If
  33.     'if Enhanced CRB Only
  34.     If Me.Controls("AppFrmISAReg" & LineNumber) = False And Me.Controls("AppFrmEnhanced" & LineNumber) = True Then
  35.         Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("EnchancedDisclosureCOST")
  36.         Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("EnchancedDisclosureADMIN")
  37.         Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("EnchancedDisclosureVAT")
  38.         Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("EnchancedDisclosureTotal")
  39.     End If
  40.      'if Enhanced CRB with ISAFirst
  41.     If Me.Controls("AppFrmISAFirst" & LineNumber) = True And Me.Controls("AppFrmEnhanced" & LineNumber) = True Then
  42.         Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("EnhancedPOVAPOCADisclosureCOST")
  43.         Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("EnhancedPOVAPOCADisclosureADMIN")
  44.         Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("EnhancedPOVAPOCADisclosureVAT")
  45.         Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("EnhancedPOVAPOCADisclosureTotal")
  46.     End If
  47.     'if Volunteer with ISA Reg
  48.     If Me.Controls("AppFrmISAReg" & LineNumber) = True And Me.Controls("AppFrmVolunteer" & LineNumber) = True Then
  49.         Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyCOST")
  50.         Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyADMIN")
  51.         Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyVAT")
  52.         Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("ISARegOnlyTotal")
  53.     End If
  54.     'if Volunteer Only
  55.     If Me.Controls("AppFrmVolunteer" & LineNumber) = True Then
  56.         Me.Controls("AppFrmCost" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureCOST")
  57.         Me.Controls("AppFrmAdmin" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureADMIN")
  58.         Me.Controls("AppFrmVAT" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureVAT")
  59.         Me.Controls("AppFrmTotal" & LineNumber) = ChargeRatesRecordSet("VolunteeDisclosureTotal")
  60.     End If
  61.     'if NoCharge Only
  62.     If Me.Controls("AppFrmNoCharge" & LineNumber) = True Then
  63.         Me.Controls("AppFrmCost" & LineNumber) = 0
  64.         Me.Controls("AppFrmAdmin" & LineNumber) = 0
  65.         Me.Controls("AppFrmVAT" & LineNumber) = 0
  66.         Me.Controls("AppFrmTotal" & LineNumber) = 0
  67.     End If
  68.  
  69.     ChargeRatesRecordSet.Close
  70.     Set ChargeRatesRecordSet = Nothing
  71.  
  72. End If
  73.  
  74. 'Calculate Totals
  75.  
  76. ' reset variables
  77. Counter = 0
  78. Me.TotalCost = 0
  79. Me.TotalVAT = 0
  80. Me.AmountDue = 0
  81.  
  82. For Counter = 1 To 10
  83. Me.TotalCost = Me.TotalCost + Nz(Me.Controls("AppFrmCost" & Counter), 0) + Nz(Me.Controls("AppFrmAdmin" & Counter), 0)
  84. Me.TotalVAT = Me.TotalVAT + Nz(Me.Controls("AppFrmVAT" & Counter), 0)
  85. Me.AmountDue = Me.TotalCost + Me.TotalVAT
  86. Next
  87.  
  88. End Sub
  89.  
Jan 29 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
I dont see anything problematic in this. How many times are you running the procedure? And how many controls do you have on your form? Are you using several "lines" controls to perform something? Most likely you should be using a subform instead, and use a running total to calculate, or a DSum.
Jan 29 '10 #7

reginaldmerritt
100+
P: 201
The form has 10 lines with a combo box to choose a record from tbCRBApplication. This then writes to the form (tbinvoices) some information from tbCRBApplication via a recordset and then writes to the form some information from tbChargeRates. This creates the invoice. The procedure above gets run everytime the user chages a combobox.

I don't see how a subform would be of use here. There are quite a few fields on the form.



Should i be doing this differently?

Therotically the tbinvoices doesn't need to have data from tables written to it as it can be linked via the PK of the other tables, I took this route encase the data needs to be exported in the future; doing it this way would just make that process easier when it comes to it.
Jan 29 '10 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
Its always hard to tell how much the the guy asking hte question knows, so I sometimes we have to assume that they don't know much (Sorry).

In a subform you would get each item on its line. If you set the subform to Continues you could have as many (or as few) lines as needed. Each time you "use" the last free line, a new line is automically added. You would also not need to fill in the data through your code. While you can do ALOT with code, I think in general its a bad idea to write code to accomplish something that access can allready do for you.

Do they text boxes on your form have a controlsource? (The boxes on the different lines, not the boxes on the top of the form.)

When the form throws an error, do you see any relation between the number of filled in lines on the form, and how often the error occurs?

I count 14 controls per line, times 10 lines, thats 140 controls, which seems alot to me, especially if they have a control source.

Other then that, I dont really know. Maybe your by accident calling your procedure several times in a row? Try putting a debug.print statement in your code, and see how many times it runs when you do a selection in your main form.

Could you show me the code from which you call the procedure?
Jan 29 '10 #9

reginaldmerritt
100+
P: 201
Yes each control is a field in tbinvoices. 148 fields in total.



This is the code i use to call in the procedure

Expand|Select|Wrap|Line Numbers
  1. Private Sub AppFrmNum1_AfterUpdate()
  2. LineNumber = 1
  3. MarkAsAdded
  4. CalculateInovice
  5. End Sub
  6.  
  7. Private Sub AppFrmNum2_AfterUpdate()
  8. LineNumber = 2
  9. MarkAsAdded
  10. CalculateInovice
  11. End Sub
  12.  
  13. ETC ETC
  14.  
Incase you want to know this is the procedure MarkAsAdded which is called in just before Calculateinvoice.

Expand|Select|Wrap|Line Numbers
  1. Private Sub MarkAsAdded()
  2.  
  3. Set CRBRecordSet = CurrentDb.OpenRecordset
  4. ("Select * FROM CRB 
  5. WHERE [FormNumber] = '" & 
  6. Me.Controls("AppFrmNum" & LineNumber) & "'")
  7.  
  8. CRBRecordSet.Edit
  9. CRBRecordSet("AddedToinvoice") = True
  10. CRBRecordSet("InvoiceNumber") = Me.InvoiceNumber
  11.  
  12. Me.Controls("AppFrmDate" & LineNumber) = 
  13. CRBRecordSet("DateCRBSent")
  14. If (CRBRecordSet("Branch")) <> "" Then 
  15. Me.Controls("AppFrmName" & LineNumber) = 
  16. CRBRecordSet("Title") & " " & CRBRecordSet("Fornames") & " " & 
  17. CRBRecordSet("Surname") & " (" & CRBRecordSet("Branch") & " )"
  18. If (CRBRecordSet("Branch")) = "" Then 
  19. Me.Controls("AppFrmName" & LineNumber) = 
  20. CRBRecordSet("Title") & " " & CRBRecordSet("Fornames") & " " & CRBRecordSet("Surname")
  21. Me.Controls("AppFrmISAReg" & LineNumber) = 
  22. CRBRecordSet("ApplyingForISAReg")
  23. Me.Controls("AppFrmISAFirst" & LineNumber) = 
  24. CRBRecordSet("WantsPOVAFIRST")
  25. Me.Controls("AppFrmStandard" & LineNumber) = CRBRecordSet("Standard")
  26. Me.Controls("AppFrmEnhanced" & LineNumber) = CRBRecordSet("Enhanced")
  27. Me.Controls("AppFrmVolunteer" & LineNumber) = CRBRecordSet("Volunteer")
  28. Me.Controls("AppFrmNoCharge" & LineNumber) = CRBRecordSet("NoCharge")
  29. Me.Controls("AppFrmPrepaid" & LineNumber) = CRBRecordSet("PaiedFor")
  30.  
  31. CRBRecordSet.Close
  32. Set CRBRecordSet = Nothing
  33.  
  34. End Sub
  35.  
Jan 29 '10 #10

reginaldmerritt
100+
P: 201
So do you think that the error message i'm getting is from wrtting to a table through a form via data taken from a recordset. As you so rightly said i could use subforms to simply display the data rather than wrtting it to the table though the form. I wouldn't need to use recordsets, however, i am working on the basis that this data will need to be exported at some point, also i want to make the program automatically create the invoice without using this form, in which case i will be back to using recordsets again.

Do you think the problem simply from too many controls on the form?
Jan 29 '10 #11

TheSmileyCoder
Expert Mod 100+
P: 2,321
I can't seem to find any other issues with your form as such. (Of course I could easily be missing something, its alot of code.)

The only other thing I can think of to try is:
Expand|Select|Wrap|Line Numbers
  1. Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("Select Top 1 * FROM ChargeRates WHERE [DateAsOf] <= #" & DateFromForm & "# ORDER BY [DateAsOf] DESC;",dbOpenSnapshot,dbOReadOnly) 
  2.  
I don't really know what the limit is on fields, but 100+ fields sound like alot.

Im also not sure why you want to use code to produce your invoice, when a report with a supreport should be able to display quite nicely what (I think) you will need.
Jan 29 '10 #12

nico5038
Expert 2.5K+
P: 3,072
The limit on tablefields is 255, but when you use long fieldnames it becomes less...

I would advise you to create a new empty database and import (with Get External date / Import) all objects. This will exclude the possibility of database corruption, which is always a no 1 suspect when strange things happen in my database....

Nic;o)
Jan 29 '10 #13

reginaldmerritt
100+
P: 201
Thanks The Smiley One i appreciate your help.

The fields in the recordset are from the tbChargeRates, there are only 30 fields in this table and recordset. I took all the controls of the form apart from one row and the same error message came up.

Sorry when i say produce the invoice though code i meant to look at which clients need invoicing, pick the appropriate products and then run a report; all from a push (or rather a click) of a button.

I'll give the ,dbOpenSnapshot,dbOReadOnly) a go and see if works.

Thanks for the tip nico5038, i'll have to give that a go also.
Jan 29 '10 #14

reginaldmerritt
100+
P: 201
SOLVED!!!

Well that was a long wild goose chase. I've been spending my time going though each section of code using the ' (rem statement marker) to block out lines of code.

Eventually i have found where the error actually is. There error occurs in these 3 lines of code.

Expand|Select|Wrap|Line Numbers
  1. CRBRecordSet.Edit
  2. CRBRecordSet("AddedToinvoice") = True
  3. CRBRecordSet("InvoiceNumber") = Me.InvoiceNumber
  4.  
This is from the MarkAsAdded procedure as above in one of my previous posts.

The missing part to the puzzle was

Expand|Select|Wrap|Line Numbers
  1. CRBRecordSet.Update
Once i've addded that after editing the recordset i get no error messages.

Does this need to be done every time a recordset is edited?

Thanks for all your help guys
Jan 29 '10 #15

TheSmileyCoder
Expert Mod 100+
P: 2,321
Yes, I believe so.

There is also something called a batchupdate, but I can't remember how exactly that works at the moment.
Jan 29 '10 #16

reginaldmerritt
100+
P: 201
Live an learn. I'll make sure i add .update from now on.

Sorry we went all around the houses there.
Jan 29 '10 #17

TheSmileyCoder
Expert Mod 100+
P: 2,321
Not your fault :P Just glad you found a solution to your problem.
Jan 29 '10 #18

NeoPa
Expert Mod 15k+
P: 31,494
@reginaldmerritt
Yes.

Both rs.New & rs.Edit put the database into an edit and therefore create a transaction. This is only closed (submitted to the database) when rs.Update is called. Many changes can be made between the two lines, but none is committed (saved) until the rs.Update tells it to.

In the mean-time, Access needs to keep a record of all the proposed changes. You can imagine how this could build up in your process if the rs.Update is never called.

I suggest a quick read of the relevant Help page (click on the .Edit line and press F1) would considerably help your understanding. They explain it better than I can.
Jan 30 '10 #19

Post your reply

Sign in to post your reply or Sign up for a free account.