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. 5 10120
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.
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
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,
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.
"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. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by black |
last post: by
|
3 posts
views
Thread by babylon |
last post: by
|
3 posts
views
Thread by Teis Draiby |
last post: by
|
reply
views
Thread by Sakharam Phapale |
last post: by
|
8 posts
views
Thread by christianlott1 |
last post: by
|
4 posts
views
Thread by =?Utf-8?B?UmljaA==?= |
last post: by
| |
7 posts
views
Thread by call_me_anything |
last post: by
|
1 post
views
Thread by Jeremy |
last post: by
| | | | | | | | | | |