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

Runtime Error 3001?!?!?!?

P: 13
I have written the following code:
Expand|Select|Wrap|Line Numbers
  1. Sub UpdateIL4010DB()
  3.     Dim rst As DAO.Recordset
  4.     Dim tbl As DAO.TableDef
  5.     Dim db As DAO.Database
  6.     Dim fld As DAO.Field
  7.     Dim indx As DAO.Index
  9.     Set db = CurrentDb
  10.     Set tbl = db.TableDefs("Rid")
  12.     Set rst = db.OpenRecordset("Rid", dbOpenTable)
  14.     rst.MoveFirst
  15.     Do
  16.         If IsNull(rst![Rcode]) Then
  17.             rst.Edit
  18.             rst![Rcode] = rst![ code ]  'Edited to avoid clash with code tags.
  19.             rst.Update
  20.         End If
  21.         rst.MoveNext
  22.     Loop Until (rst.EOF)
  23.     rst.Close
  25. End Sub
The number of records to be read is about 2000000 records, and the runtime error occurs somewhere in the middle.

At first i thought the problem was the database becoming too large with too much data, causing the problem, but then i realised it jumps to over 2GB once the problem occurs. Which means, the problem caused the large size, instead of the other way round.

So i need to know why it caused runtime '3001' in order to make the program run successfully. Please help me!!

Thank you!!!
Dec 9 '08 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,287
Try adding error handling and put a breakpoint in the code to take a look at the record at which the error occurs.
Dec 9 '08 #2

Expert Mod 15k+
P: 31,489
It always helps to include the Error MESSAGE when requesting assistance. I won't insult you by explaining why.

However, it seems clear that you are falling over an Access size problem. Like most database systems it will add changes to the log until the transaction is completed. When it is deemed to have executed fully and without error, it then transfers the updates to the database proper. Clearly this takes up a great deal of space.

I would seriously consider using a query to do this job instead of code. Example code is below. I would ensure that the execution properties are set to 'Use Transaction' = No.

NB. In the following SQL your field "Code" is displayed as having spaces around it to avoid clashing with the site's [ CODE ] tagging. I suggest you always avoid use of such names that are likely to be reserved words anywhere in future.
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Rid]
  3. SET [Rcode]=[ code ]
  5. WHERE [Rcode] Is Not Null
Welcome to Bytes!
Dec 9 '08 #3

Expert 5K+
P: 8,636
Hello NeoPa, correct me if I am wrong but the code that the OP is displaying is not encapsulated within a Transaction, so there is no Transaction Log per say. The Edit method will copy the Current Record to the copy buffer, and the Update method saves the data from the copy buffer to the Database. The MoveNext Method will discard the contents of the copy buffer (it is not cumulative, is it?). What do you think of the idea to handle this Update in 2 separate processes, namely; create the Recordset, Update the 1st 1,000,000 Records, close the Recordset, then set its Object Variable to Nothing. Duplicate this process for the last 1,000,000 Records. If the Error does not occur, then it is probably a size limitation. Just thinking out loud! (LOL).

P.S. - The funny part is that Error 3001 is defined as: Application-defined or object-defined error', but you know how those Error Messages can sometimes be! (LOL).
Dec 9 '08 #4

Expert Mod 15k+
P: 31,489
In truth, I'm not sure ADezii.

Sometimes transaction processing can be implied, and not very obvious. In this case I was working out (guessing) from the results described.

It could be transaction memory, or it could be poor handling of used, but no longer required, space in Access.

Whichever it turns out to be, the suggestion of doing it in smaller chunks is probably a good one. Let's see how it goes from here.
Dec 16 '08 #5

Post your reply

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