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

Run-time error 3003 - Storing Values from Form to Table

P: 3
I'm encountering an error in MS Access 2007 when I try to store values from a form to a table. Here's a snippet of the VB:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Submit_Update_Click()
  2. Dim DB As Database
  3. Dim new_comment As Recordset
  4. Set DB = CurrentDb()
  5.  
  6. Set new_comment = DB.OpenRecordset("tbl_History", dbOpenDynaset)
  7. new_comment.AddNew
  8.  
  9. new_comment("AWT") = AWT.Value
  10. new_comment("Issue_Criticality") = Issue_Criticality.Value
  11.  
  12. new_comment.Update
  13. new_comment.Close
  14.  
  15. Done1:
  16. AWT.Value = Null
  17. Issue_Criticality.Value = Null
  18.  
  19. Done:
  20. DB.Close
  21.  
  22. End Sub
  23.  
I do have more than two fields being stored, but it didn't seem necessary to list them all - 12 total. The idea is that the user will click the 'Submit' button (the above code being the event) and thereafter, the values get stored to the table and the form clears the entered values for a subsequent entry.

The run-time error '3003' says "Could not start transaction; too many transactions already nested". Does that mean I'm trying to store too many input values?
May 5 '10 #1

✓ answered by Jim Doherty

Try fully qualifying your object library and closing all of your object variables down at the end of your code sequences and setting them to nothing. I suspect your are leaving all sorts of references,recordsets and other stuff open in memory with your workspace as you are testing/building. (I can see you have closed in this example and db). One way of testing if this is the cause is by exiting out of access and restarting

Just my two cents observation here....have you considered just returning one row recordset if all you are doing is adding a new one? why not just call...

"Select top 1 * from tbl_History"

or

"Select * from tbl_History where 1=2"

This will save memory and better over a network as you are not dragging anything up (network... split backend etc) and will be faster as your database grows in size.

Share this Question
Share on Google+
2 Replies


Jim Doherty
Expert 100+
P: 897
Try fully qualifying your object library and closing all of your object variables down at the end of your code sequences and setting them to nothing. I suspect your are leaving all sorts of references,recordsets and other stuff open in memory with your workspace as you are testing/building. (I can see you have closed in this example and db). One way of testing if this is the cause is by exiting out of access and restarting

Just my two cents observation here....have you considered just returning one row recordset if all you are doing is adding a new one? why not just call...

"Select top 1 * from tbl_History"

or

"Select * from tbl_History where 1=2"

This will save memory and better over a network as you are not dragging anything up (network... split backend etc) and will be faster as your database grows in size.
May 5 '10 #2

P: 3
Jim, thanks for the tips. I will look into this.
May 6 '10 #3

Post your reply

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