Turn off UNDO at action queries ?

HS Hartkamp

Hi all,

I am working with fairly large databases (> 500 Mb / < 2,000,000 rexcords),
and often need to do action queries on these.

I have the feeling that much of the computing power is going into building a
table of changes so that you can be prompted "Are you sure to change xx
records", and be able to reply no.
I regularly get messages that say "Insufficient memory or disk space, you
won't be able to undo; continue?"

Often I do not need this, and it would seriously speed up things if I can
run action queries without this Undo.

Is there a way to do so ?

Thanks in advance,

Bas Hartkamp.

Apr 1 '06 #1

tina

if you run the action query from a macro, you can call a SetWarnings action
before the OpenQuery action; just set the Warnings On property to No. when
the macro closes, warnings will automatically be turned back on.

if you run the action query from code, you can turn warnings off before
running the query, and then *turn warnings back on* - Access will not
automatically turn warnings back on when they're turned off using VBA code.

hth
"HS Hartkamp" <ha**************@wanadoo.nl> wrote in message
news:44**********************@news.wanadoo.nl...

Hi all,

I am working with fairly large databases (> 500 Mb / < 2,000,000 rexcords), and often need to do action queries on these.

I have the feeling that much of the computing power is going into building a table of changes so that you can be prompted "Are you sure to change xx
records", and be able to reply no.
I regularly get messages that say "Insufficient memory or disk space, you
won't be able to undo; continue?"

Often I do not need this, and it would seriously speed up things if I can
run action queries without this Undo.

Is there a way to do so ?

Thanks in advance,

Bas Hartkamp.

Apr 2 '06 #2

Tim Marshall

tina wrote:

if you run the action query from a macro, you can call a SetWarnings action
before the OpenQuery action; just set the Warnings On property to No. when
the macro closes, warnings will automatically be turned back on.

Forgive me, but I must step in here:

Do NOT rely on the closing of the macro to reset warnings. Repeat, DO
NOT do this!!!

Make sure you have a final set warnings = yes macro line.

However, I would never turn off warnings in a macro. If an error does
occur, such as if your action query syntax is incorrect or in error,
there is no error handling in macros and setWarnings will remain off.

This can have disastrous consequences later.

If you are just working your self and no one is using your application,
then I would just put up with the warning messages. If, however, you
have users other than yourself working on your mdb that are not very
knowledgeable in how to use Access, then you need to write code, using
the execute method with the dbFailOnError option.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Apr 3 '06 #3

Bob Quintal

Tim, Tina,

If you go to the original poster's message you will find that
Warnings isn't the user's problem. It's a transaction problem.

HS,

set the query's UseTransaction property to NO.

Q

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:e0**********@coranto.ucs.mun.ca:

tina wrote:

if you run the action query from a macro, you can call a
SetWarnings action before the OpenQuery action; just set the
Warnings On property to No. when the macro closes, warnings
will automatically be turned back on.

Forgive me, but I must step in here:

Do NOT rely on the closing of the macro to reset warnings.
Repeat, DO NOT do this!!!

Make sure you have a final set warnings = yes macro line.

However, I would never turn off warnings in a macro. If an
error does occur, such as if your action query syntax is
incorrect or in error, there is no error handling in macros
and setWarnings will remain off.

This can have disastrous consequences later.

If you are just working your self and no one is using your
application, then I would just put up with the warning
messages. If, however, you have users other than yourself
working on your mdb that are not very knowledgeable in how to
use Access, then you need to write code, using the execute
method with the dbFailOnError option.

--
Bob Quintal

PA is y I've altered my email address.

Apr 3 '06 #4

HS Hartkamp

Bob,

This is amazing, and so simple. Part of my gratitude is because I have been
looking for this answer -off and on- for about a year and a half. Also, it's
nice to learn something new about Access or Excel every once in a while.

Indeed, it is a transaction thing, and I have -untill now- not properly
looked at the query properties. The speed difference is significant: In a
simple test query it took 46 seconds with transaction, and only 24 seconds
without transaction. That is nearly a 50% reduction.

As my work has many queries where the transaction is pointless, this will
significantly speed up things (and reduce my smoking as I have less forced
breaks in my work).

Thanks !

Bas Hartkamp.
"Bob Quintal" <rq******@sympatico.ca> schreef in bericht
news:Xn**********************@207.35.177.135...

Tim, Tina,

If you go to the original poster's message you will find that
Warnings isn't the user's problem. It's a transaction problem.

HS,

set the query's UseTransaction property to NO.

Q

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:e0**********@coranto.ucs.mun.ca:

tina wrote:

if you run the action query from a macro, you can call a
SetWarnings action before the OpenQuery action; just set the
Warnings On property to No. when the macro closes, warnings
will automatically be turned back on.

Forgive me, but I must step in here:

Do NOT rely on the closing of the macro to reset warnings.
Repeat, DO NOT do this!!!

Make sure you have a final set warnings = yes macro line.

However, I would never turn off warnings in a macro. If an
error does occur, such as if your action query syntax is
incorrect or in error, there is no error handling in macros
and setWarnings will remain off.

This can have disastrous consequences later.

If you are just working your self and no one is using your
application, then I would just put up with the warning
messages. If, however, you have users other than yourself
working on your mdb that are not very knowledgeable in how to
use Access, then you need to write code, using the execute
method with the dbFailOnError option.

--
Bob Quintal

PA is y I've altered my email address.

Apr 16 '06 #5

Bob Quintal

"HS Hartkamp" <ha**************@wanadoo.nl> wrote in
news:44***********************@news.wanadoo.nl:

Bob,

This is amazing, and so simple. Part of my gratitude is
because I have been looking for this answer -off and on- for
about a year and a half. Also, it's nice to learn something
new about Access or Excel every once in a while.

Indeed, it is a transaction thing, and I have -untill now- not
properly looked at the query properties. The speed difference
is significant: In a simple test query it took 46 seconds with
transaction, and only 24 seconds without transaction. That is
nearly a 50% reduction.

As my work has many queries where the transaction is
pointless, this will significantly speed up things (and reduce
my smoking as I have less forced breaks in my work).

Thanks !

Bas Hartkamp.

You are very welcome, sir.

Now, in order to please Steve, the PCD (Pretty Crappy Developer)
fellow, I must insist that you export your queries to a
spreadsheet one cell at a time, taking an hour or more. This is
not to give you time for a smoke (and I do profit from long
queries to go for a smoke) but to go for a delightful full meal,
with a few pints.

Q.
"Bob Quintal" <rq******@sympatico.ca> schreef in bericht
news:Xn**********************@207.35.177.135...

Tim, Tina,

If you go to the original poster's message you will find that
Warnings isn't the user's problem. It's a transaction
problem.

HS,

set the query's UseTransaction property to NO.

Q

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:e0**********@coranto.ucs.mun.ca:

tina wrote:

if you run the action query from a macro, you can call a
SetWarnings action before the OpenQuery action; just set
the Warnings On property to No. when the macro closes,
warnings will automatically be turned back on.

Forgive me, but I must step in here:

Do NOT rely on the closing of the macro to reset warnings.
Repeat, DO NOT do this!!!

Make sure you have a final set warnings = yes macro line.

However, I would never turn off warnings in a macro. If an
error does occur, such as if your action query syntax is
incorrect or in error, there is no error handling in macros
and setWarnings will remain off.

This can have disastrous consequences later.

If you are just working your self and no one is using your
application, then I would just put up with the warning
messages. If, however, you have users other than yourself
working on your mdb that are not very knowledgeable in how
to use Access, then you need to write code, using the
execute method with the dbFailOnError option.

--
Bob Quintal

PA is y I've altered my email address.

--
Bob Quintal

PA is y I've altered my email address.

Apr 16 '06 #6