Automatically run Make table query

[Guest]

Can make table query run automatically?

I produced a report based on make table queries. I want the make table queries run automatically whenever I run the report, so that I don’t need to go to each make table query to update information, then run the report. I guess I will need to write some kind of code. I don’t have experience with coding. Could you please help?

Thanks

 

[Wayne Morgan]

In the report's Open event you could use the

CurrentDb.Execute strSQL, dbFailOnError

command where strSQL is a string variable that has the SQL of your make
table query. This can also be replaced with the name of a stored query. The
dbFailOnError will cause an error to be returned if the query fails. For a
Make Table query to run, the table must not yet exist, so you may need to
delete it first.

Another option, if the structure of the table doesn't change, is just leave
the table and delete its data then use an Append Query to add the new data.

--
Wayne Morgan
Microsoft Access MVP

Can make table query run automatically?

I produced a report based on make table queries. I want the make table

queries run automatically whenever I run the report, so that I don't need to
go to each make table query to update information, then run the report. I
guess I will need to write some kind of code. I don't have experience with
coding. Could you please help?

 

[Guest]

Thanks, Wayne. I tried the code. It works. The only trouble is that I will need to delete the table whenever I re-run the report. I guess I can delete the table on the close report event. Could you please help me write the code? (I have no coding experience) Thanks:


----- Wayne Morgan wrote: ----

In the report's Open event you could use th

CurrentDb.Execute strSQL, dbFailOnErro

command where strSQL is a string variable that has the SQL of your mak
table query. This can also be replaced with the name of a stored query. Th
dbFailOnError will cause an error to be returned if the query fails. For
Make Table query to run, the table must not yet exist, so you may need t
delete it first

Another option, if the structure of the table doesn't change, is just leav
the table and delete its data then use an Append Query to add the new data

--
Wayne Morga
Microsoft Access MV

Can make table query run automatically

queries run automatically whenever I run the report, so that I don't need t
go to each make table query to update information, then run the report.
guess I will need to write some kind of code. I don't have experience wit
coding. Could you please help

 

[Wayne Morgan]

To delete a table:

DoCmd.DeleteObject acTable, "TableName"

If the table doesn't exist when you try to delete it you will receive error
#7874. You could trap this in your error handler or you could loop through
the TableDefs collection and see if the table name exists before you try to
delete it. If you always create the table when opening the report and delete
it when closing the report, you should be ok as long as nothing happens
(i.e. computer locks up) to cause the report to close abnormally and
therefore not clean up as it closes.

Does your table structure change or is it the same table you're recreating
each time? If the latter, it would probably be easier just to leave the
table and run a delete query followed by an append query when you start the
report. If the table is empty and you run a delete query, you won't get an
error.

--
Wayne Morgan
Microsoft Access MVP

Thanks, Wayne. I tried the code. It works. The only trouble is that I will

need to delete the table whenever I re-run the report. I guess I can delete
the table on the close report event. Could you please help me write the
code? (I have no coding experience) Thanks

 

[Wayne Morgan]

You can execute either a SQL statement or a query that you've made using the
query design grid. If you prefer using the SQL, you can still use the query
design grid to make the query, then change to SQL view and copy and paste
the text into your code. Running the query would use the same command I've
already given you. The delete query is very simple

DELETE * FROM tblMyTable;

An append query SQL looks something like this

INSERT INTO tblTest SELECT Table1.* FROM Table1;

This will take all of the data in Table1 and copy it into the associated
fields in tblTest.

To make different types of queries in the query design grid, select the
appropriate query type from the Query menu or use the toolbar button.

Another thing to be aware of, regardless of how you do this (delete/append
or create/delete table) the database will "grow" because deleted items
aren't really gone until you compact the database.

--
Wayne Morgan
MS Access MVP

How to run a delete query and an append query in report open event? I

don't know how to write the code