Not enough disk space or memory

[Tom Ellison]

In trying to automate a series of steps in VBA, I have a step:

DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")

I get a warning with which I'm not familiar:

There isn't enough disk space or memory to undo the data changes this action
query is about to make.

Do you want to run this action query anyway?
For information on freeing disk space or freeing memory, search the
Microsoft Windows Help index for 'disk space, freeing or 'memory,
troubleshooting.'

Yes No

This confuses me. Under what conditions will access undo the results of an
action query after it has been performed?

I have tens of gigabytes available on the only volume this computer
posseses. I presume that isn't the problem, so it must be that my memory is
short. I have a gigabyte of ram and there's nothing else running on my XP
system but Access, and a few rather standard background things. I'm saying
I have a large amount of free memory.

Anyway, what is this about? I'd like to have it not stop at all. I'm
trying to get a long list of things done while I sleep.

And thanks very much.

Tom Ellison

 

[Ken Snell \(MVP\)]

Tom -

That is one of the "built-in" warnings that ACCESS can display when running
action queries. It simply means that Jet won't be able to "remember" all the
changes being made because there are too many records being affected, so it
warns you that it cannot undo the action should an error occur partway
through the query's run.

You can turn that message off by the SetWarnings action before you run the
query:

DoCmd.SetWarnings False
DoCmd.RunSql("DELETE * FROM SomeTable WHERE SomeColumn = "Some Value")
DoCmd.SetWarnings True

 

[Tom Ellison]

Hey Key,

Thanks.

I hadn't thought of the fact that Access can back out if an error occurs
part way through. I'm sure now you're right about this.

I can use the SetWarnings. But for my purpose, I'd like to just turn them
off, permanently. I was looking thought the options for this. Maybe I'll
find it now.

See you soon, I hope.

Tom

 

['69 Camaro]

Hi, Tom.

I can use the SetWarnings. But for my purpose, I'd like to just turn them
off, permanently.

You don't need to turn them off to avoid these messages. Try:

CurrentDb().Execute "DELETE * " & _
"FROM SomeTable " & _
"WHERE SomeColumn = 'Some Value';", dbFailOnError


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Ken Snell \(MVP\)]

Tools | Options | Edit/Find (tab) | Confirm

 

['69 Camaro]

Hi, Tom.

Under what conditions will access undo the results of an action query
after it has been performed?

Access will undo the results of an action query whenever the transaction is
rolled back. This can happen whenever an error occurs while the action
query is running during an implicit transaction, or when the operation
occurs inside an explicitly declared transaction, and then the Rollback
command is issued instead of CommitTrans.

I have tens of gigabytes available on the only volume this computer
posseses. I presume that isn't the problem, so it must be that my memory
is short.

This generally is not the case. A temporary 2 GB database file is created
on your hard drive to hold the data from the transaction until it's
finished, whereupon the results are placed in your database file. Your
query is exceeding the allotted space for the transaction within this
temporary database file, so Jet is asking your permission to recycle the
empty data pages in order to compensate.

I'd like to have it not stop at all. I'm trying to get a long list of
things done while I sleep.

There are several ways to avoid this problem, either within VBA code (as
suggested elsewhere in this thread), or via the Windows Registry by changing
the MaxLocksPerFile option, or setting the query's UseTransaction Property
to No. This last choice is generally not the preferred method, as any
changes made to the data during the operation will remain, even if the query
fails at some point.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Wickerath]

To add some to Gunny's excellent advice, please see the following KB article:

You may receive a "There isn't enough disk space or memory" error message
when you perform an operation on an Access table

http://support.microsoft.com/kb/286153/


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

['69 Camaro]

Hi, Ken.

Tools | Options | Edit/Find (tab) | Confirm

Turning off the confirmation messages won't affect whether or not the lack
of disk space/memory confirmation message appears while running an action
query. This situation will still require manual input from the user before
proceding with the rest of the action query. Two ways to avoid this message
while running an action query in VBA is to either turn warnings off before
running the RunSQL method, as you suggested, or to use the Execute method
with the dbFailOnError argument, as I suggested. The Execute method forces
Jet to allocate more space in the temporary 2 GB database file where it
writes the data for the transaction, thereby avoiding the "Not enough disk
space or memory" message.

There are a few other benefits to using the Execute method rather than the
RunSQL method, as listed by Allen Browne on the following Web page,
including the fact that the query runs faster:

http://groups.google.com/group/micr...5290/fbed3995447ec827?&hl=en#fbed3995447ec827

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.