Make Table Query Popups

[Rubie]

Hello,

I created a Make Table Query named (Test Table) based on another query that
pulls data from a table called (Table). When I run the Make table query a
dialog box pops up that states "The existing table 'Test Table' will be
deleted before you run the query. Do you want to continue?"

How do I get the query to run without asking the question? I went to
(tools-options-edit/find) and took the check marks out of everything in the
Confirm section and that stopped one of the popups but not the popup
mentioned above.

Any suggestions would be greatly appreciated.

 

[ghetto_banjo]

Either in the VB Code or in a macro you need to turn off the warnings
before running the query.

DoCmd.SetWarnings = False

 

[ghetto_banjo]

actually probably without that equals sign

DoCmd.SetWarnings False

 

[Michel Walsh]

Deleting the table before running the make table query is possible in your
case?

Vanderghast, Access MVP

 

[Rubie]

Thanks for responding ghetto. I checked my macro and the SetWarnings is set
to the default (NO), but the YES/No dialog box still pops up.

 

[Rubie]

Hi Michel,

When I manually click the Yes button in the dialog box the old table is
replaced with a new one and the data is updated. For example: the old table
has 10 records and when the new table comes in it has 15 records. Which is
what I want it to do, but I want the Yes button to be pressed automatically.
Is that possible?

 

[Michel Walsh]

You won't have the dialog in the first place AT ALL if the table you try to
create does NOT already exist. That is why I asked if you can delete that
table BEFORE running the MAKE TABLE query.


Vanderghast, Access MVP

 

[John W. Vinson]

Hello,

I created a Make Table Query named (Test Table) based on another query that
pulls data from a table called (Table). When I run the Make table query a
dialog box pops up that states "The existing table 'Test Table' will be
deleted before you run the query. Do you want to continue?"

How do I get the query to run without asking the question? I went to
(tools-options-edit/find) and took the check marks out of everything in the
Confirm section and that stopped one of the popups but not the popup
mentioned above.

Any suggestions would be greatly appreciated.

I wouldn't use a maketable query AT ALL in this case. Instead, run a query to
delete all records from [Test Table] and change your maketable into an Append
query. This will be faster, bloat less, and not give you this pesky error
message.

 

[Rubie]

Worked great John!! Thanks.

The problem that I have now is that the query that pulls data from a table
called (Table) needs to open and then close again so that the Append query
can get the updated data from it. I have a macro setup to (open and close)
the query but it will not close. I hope this makes sense. Thanks.

Hello,

I created a Make Table Query named (Test Table) based on another query that
pulls data from a table called (Table). When I run the Make table query a
dialog box pops up that states "The existing table 'Test Table' will be
deleted before you run the query. Do you want to continue?"

How do I get the query to run without asking the question? I went to
(tools-options-edit/find) and took the check marks out of everything in the
Confirm section and that stopped one of the popups but not the popup
mentioned above.

Any suggestions would be greatly appreciated.

I wouldn't use a maketable query AT ALL in this case. Instead, run a query to
delete all records from [Test Table] and change your maketable into an Append
query. This will be faster, bloat less, and not give you this pesky error
message.

 

[John W. Vinson]

Worked great John!! Thanks.

The problem that I have now is that the query that pulls data from a table
called (Table) needs to open and then close again so that the Append query
can get the updated data from it. I have a macro setup to (open and close)
the query but it will not close. I hope this makes sense. Thanks.

Huh? You don't "open" the query or "close" it. An action query RUNS and then
goes away, using the current values in its source table. I would expect your
macro to run two queries - one of them looking like

DELETE * FROM temptable;

and the other

INSERT INTO temptable SELECT field, field, field FROM Table WHERE <some
criteria>;

What are the actual steps of your macro, and the SQL of the query?