Runtime Error 3001?!?!?!?

[kwokv616]

I have written the following code:

Sub UpdateIL4010DB()
    
    Dim rst As DAO.Recordset
    Dim tbl As DAO.TableDef
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim indx As DAO.Index
      
    Set db = CurrentDb
    Set tbl = db.TableDefs("Rid")
    
    Set rst = db.OpenRecordset("Rid", dbOpenTable)
    
    rst.MoveFirst
    Do
        If IsNull(rst![Rcode]) Then
            rst.Edit
            rst![Rcode] = rst![ code ]  'Edited to avoid clash with code tags.
            rst.Update
        End If
        rst.MoveNext
    Loop Until (rst.EOF)
    rst.Close
  
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!!!

[ChipR]

Try adding error handling and put a breakpoint in the code to take a look at the record at which the error occurs.

[NeoPa]

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.

UPDATE [Rid]

SET [Rcode]=[ code ]

WHERE [Rcode] Is Not Null

Welcome to Bytes!

[ADezii]

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.

UPDATE [Rid]

SET [Rcode]=[ code ]

WHERE [Rcode] Is Not Null

Welcome to Bytes!

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).

[NeoPa]

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.