Turn off UNDO at action queries ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HS Hartkamp - 01 Apr 2006 23:20 GMT

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.

Reply to this Message

tina - 02 Apr 2006 01:39 GMT

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

> Hi all,
>
[quoted text clipped - 15 lines]
>
> Bas Hartkamp.

Reply to this Message

Tim Marshall - 03 Apr 2006 14:26 GMT

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

Signature

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

Reply to this Message

Bob Quintal - 03 Apr 2006 22:58 GMT

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

>> if you run the action query from a macro, you can call a
>> SetWarnings action before the OpenQuery action; just set the
[quoted text clipped - 21 lines]
> use Access, then you need to write code, using the execute
> method with the dbFailOnError option.

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message

HS Hartkamp - 16 Apr 2006 21:16 GMT

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.

> Tim, Tina,
>
[quoted text clipped - 32 lines]
>> use Access, then you need to write code, using the execute
>> method with the dbFailOnError option.

Reply to this Message

Bob Quintal - 16 Apr 2006 21:29 GMT

> Bob,
>
[quoted text clipped - 16 lines]
>
> 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.

>> Tim, Tina,
>>
[quoted text clipped - 33 lines]
>>> to use Access, then you need to write code, using the
>>> execute method with the dbFailOnError option.

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message