Query generating Warning Message

[tck0633]

I have written an update query that updates ~224,000 records from another large table. When I am running the query, I get this message from Access: "There is not enough disk space or memory to undo the data changes this action query is about to make." It then prompts me to either continue or cancel the query. I normally will just continue and the table will be updated. What I am afraid of is if there is an issue and I cannot roll back the updates. I have researched this message to some extent and most sites say to up the number on the MaxLocksPerFile in the registry - which I have upped to 1000000 - but the message persists. Any suggestions?

[ranman256]

Did you turn off messaging?
setWarnings False

[tck0633]

I'm not sure how to do that outside of VBA. Besides, I want to be able undo the data changes if there is a problem. This may not be possible with tables this large, but I wanted to see if there was a way to set up the environment so I COULD undo changes if necessary.

[ssanfu]

How full is your disc? How many fields are being updated? Updating ~224,000 records with 2 fields requires less space than ~224,000 records with 20 fields.

Can you break up the records to do 112,000 records at a time? Or maybe do 75,000 records at a time? (Try on a COPY of the dB.)

[CJ_London]

I want to be able undo the data changes if there is a problem

Think you need to clarify, you can't undo the data changes at a later time. You either get the message 'There is not enough disk space....' part way through the update or you get the message something like '224000 records to be updated, xx failed due to key errors, xx due to …' just before the update is actioned. Once you press yes you are committed, you cannot subsequently undo.

Only way you could do it is to make a copy of the target table, do your update and make your checks. If not happy replace the target table with the copy to put you back to where you were.

You might want to investigate transaction processing - see this link to get you started https://msdn.microsoft.com/en-us/vba...-dao-recordset but it relates to running SQL via code, not running a query directly, and I'm not sure it would address your particular issue anyway.