473,223 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,223 software developers and data experts.

Turn off UNDO at action queries ?


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
5 10563
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
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
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

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: black | last post by:
I'm coding with Tkinter and i wonder whether we could get current OS' clipboard available, and event more, anyone can inspires me how we can achieve undo and redo function ? thanx~ ...
3
by: babylon | last post by:
any facilities in csharp that can help me implmenting undo/redo in my application? thx
3
by: Teis Draiby | last post by:
I'm looking for some information (books, articles, tutorials) on how to implement a multiple undo/redo framework. I'm a beginner in this so I prefer information specifically targeting C# with code...
0
by: Sakharam Phapale | last post by:
Hi All, I am developing my own text editor using RichTextBox control. I don't want to use RichTextBox Undo and Redo functions. Instead of that I want to develop my own algorithm for Undo & Redo,...
8
by: christianlott1 | last post by:
After searching the group and the net, I just can't believe after all this work I'm going to have to sit through three Write Conflict messages when I want to roll back and delete a record. The...
4
by: =?Utf-8?B?UmljaA==?= | last post by:
Hello, Does vb2005 have a built-in UnDo feature / object for applications so that I can undo actions like other windows apps? Or do I have to write my own UnDo routine? If vb2005 does have a...
3
by: Reedsp | last post by:
OS using: MS XP MS Access: 2003 SP2 Is their any way to turn on or off confirmation messages through VB for record changes and action queries without using the menu bar File | Options | Edit/Find...
7
by: call_me_anything | last post by:
Hi, I am looking for a good algorithm (in C/C++) for implementing undo/ redo on a data structure. The data structure is basically a n-children tree somewhat of the form : class /* or...
1
by: Jeremy | last post by:
I'm working on an application that does some DOM manipulation. I want to add a mechanism for "Undo" so that the user can revert to the previous state after performing a mistaken action. Simple...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.