make-table query

[SP]

Hi

I'm using Access 2003, and I have a make-table query. Every time I run the
query, there are about 3 messages with various warnings (saying the table
will be modified, the table will be deleted, and so many rows will be
pasted).

Can I turn these messages off - by some settings or by programming ??

Thanks
Steve

 

[John Vinson]

Hi

I'm using Access 2003, and I have a make-table query. Every time I run the
query, there are about 3 messages with various warnings (saying the table
will be modified, the table will be deleted, and so many rows will be
pasted).

Can I turn these messages off - by some settings or by programming ??

Why are you running MakeTables at all? In my experience they are VERY
rarely needed.

One alternative would be to run the query from a macro or (better)
from VBA code, and put

DoCmd.SetWarnings False

before running the query. Be sure to SetWarnings True afterward.

More efficient than a MakeTable would be to have a stored table, and
use a Delete query to empty it and an Append query to fill it (you'll
again need to use the Setwarnings).

More efficient yet, if you're doing this in order to report, export,
edit or display the data, would be to simply use a Select query as the
basis for your report, export, or form. The MakeTable (or even the
Append) will simply slow things down and bloat your database and is
usually not necessary.

John W. Vinson[MVP]

 

[SP]

I really appreciate your detailed reply.

I used make-table query to generate data for a report. I had used select
query before that, and the report would give some error about sub-group (or
multi group...something like that which I don't have available to let you
know at the moment).

Perhaps make-table is not efficient, but from my test run, it did work over
the select query. So, that's a progress for now.

I'll keep your Stored Table suggestion in mind and read more about it
eventually. Do you have a simple example on this ?

Thanks again for the SetWarnings method.
Steve

 

[John Vinson]

I really appreciate your detailed reply.

I used make-table query to generate data for a report. I had used select
query before that, and the report would give some error about sub-group (or
multi group...something like that which I don't have available to let you
know at the moment).

Well... rather than busting yourself getting the MakeTable to work,
perhaps you should fix the query; if you can base the report on a
Table, then *you can base the report on a Query*. Care to post the SQL
of the query that didn't work and the error message?

Perhaps make-table is not efficient, but from my test run, it did work over
the select query. So, that's a progress for now.

I'll keep your Stored Table suggestion in mind and read more about it
eventually. Do you have a simple example on this ?

Just leave your make-table "made table" undeleted, and change the
MakeTable query to an Append query. Then create a query

Delete * FROM tablename;

to empty it. Use a Macro or (again, better) VBA code to SetWarnings
True; run the Delete query; run the Append query; run your report;
then (if desired) run the Delete query again.

Compact your database regularly if you're doing this with any
frequency or substantial size of report!

John W. Vinson[MVP]

 

[SP]

Thank you for your help. Let me get to the file and post the SQL here.
I'll be back.

Thanks
Steve