How do I stop Access from asking confirmation to delete a table in a Make-Table Query?

[Mike Webb]

Using Access 2002.

My startup form has a macro attached to run a Make-Table Query. I am unable
to figure out how to tell Access to stop asking me for
confirmation/permission to delete the table this query makes (from previous
running of the macro) so it can then create a new one. I've unchecked the 3
Confirm Edit checkboxes on the Edit/Change tab of the Options window. What
am I missing?

TIA,
Mike

 

[Guest]

Add the following two steps - one before and other after
the make table. This turns off all safety measures.

SetWarnings The arguement defaults to No.

SetWarnings Change the default to Yes.

 

[John Vinson]

Using Access 2002.

My startup form has a macro attached to run a Make-Table Query. I am unable
to figure out how to tell Access to stop asking me for
confirmation/permission to delete the table this query makes (from previous
running of the macro) so it can then create a new one. I've unchecked the 3
Confirm Edit checkboxes on the Edit/Change tab of the Options window. What
am I missing?

The short answer is that you can put a line

SetWarnings False

before running the macro step which deletes the table and runs the
maketable query; BE SURE to put

SetWarnings True

after the queries run, or you'll turn off ALL warning messages for the
rest of the Access session!

The long answer is: WHY are you running make-table queries AT ALL? If
you just create a Select query which returns the same record, you can
use it as the recordsource for a Form or a Report; you can export it;
you can base another query on it; you can do just about anything that
you can do with a Table, without the bloat and inefficiency of a
make-table query. Is it really necessary to do this at all?

 

[Mike Webb]

Thanks for the advice. I'll certainly use it.

As for my reason for the Make-Table query ... I'm still new at Access and I
couldn't figure a way using the wizards or my limited SQL knowledge to tell
Access to bring the data together with out a SELECT statement. Near as I
can tell, I can only use SELECT's on tables - hence my make-table query.

I'm certainly open to a better way of doing this.

Mike

 

[John Vinson]

Thanks for the advice. I'll certainly use it.

As for my reason for the Make-Table query ... I'm still new at Access and I
couldn't figure a way using the wizards or my limited SQL knowledge to tell
Access to bring the data together with out a SELECT statement. Near as I
can tell, I can only use SELECT's on tables - hence my make-table query.

If you just change your MakeTable query back to a Select query it will
contain EXACTLY THE SAME information as your new table contains. A
select query is *simpler* than a maketable query, not more difficult;
it's also much faster since Access doesn't need to create new records
in the systems table, new indexes, etc.

And you can create Queries (a Query contains a SELECT clause, but it's
a query, not a SELECT) based on other Queries.

If you'ld like to post the SQL view of your MakeTable query and
describe what you're trying to accomplish with it, I'm sure there is a
solution not involving make-tables!