Auto accept changes to make table queries

[Guest]

I have a large database that has several queries upon other queries. In order
to bypass exceeding the system resources I created a make table named 'temp'
to run new queries off of. The problem is that on the end user side when you
run the query, it prompts the question "The existing table 'temp' will be
deleted before you run the query". The answer is always going to be "yes" as
the data will always be replaced. I have already unchecked the confirm Record
Changes, Document deletions, and Action Queries under the options menu.

How do I get access to not prompt this question for the make table or auto
accept the changes?

 

[oldguy]

Hi Sirkus,

If I have not misunderstood your question, the answer lies in a macro, or
VBA:

The logic is
set warnings off
<run your make table query>
set warnings on

With VBA, the format should be docmd.setwarnings false/true
I can't remember the exact format for macros, but it should be evident when
you start writing the macro.

Good luck,

Engin

 

[Nikos Yannacopoulos]

Make a macro like:

Action: Argument
SetWarnings No
OpenQuery The name of the make-table query
SetWarnings Yes

and run this macro, instead of the query itself. The first action will
bypass the warnings. The last one resets them, which Access would do
anyway at the end of the query execurion; the reason I consider good
practice to put it there anyway is so it's not missed out if the macro
is converted to code, for, unlike in macros, warnings are not
automatically turned back on if turned off in code.

HTH,
Nikos

 

[=?iso-8859-1?Q?J=F6rg_Ackermann?=]

Hi,

sirkus schreibselte:

I have a large database that has several queries upon other queries.
In order to bypass exceeding the system resources I created a make
table named 'temp' to run new queries off of. The problem is that on
the end user side when you run the query, it prompts the question
"The existing table 'temp' will be deleted before you run the query".
The answer is always going to be "yes" as the data will always be
replaced. I have already unchecked the confirm Record Changes,
Document deletions, and Action Queries under the options menu.

How do I get access to not prompt this question for the make table or
auto accept the changes?

you'll have to check if table exists and delete it first

If DCount("*", "MSysObjects", "[Name]='tblTest'") > 0 Then _
CurrentDb.Execute "DROP TABLE tblTest;"
....


Acki