Automate changing table in query

  • Thread starter Thread starter zaps via AccessMonster.com
  • Start date Start date
Z

zaps via AccessMonster.com

Hi Everyone,

I have a query that I need to run each month. Before I run it, I need to
manually switch last months table to the new months table. Each month a new
table is imported to the database for the month (we would like to keep each
month in a separate table for now) and I need to switch the previous month's
table in the query to the new month's table. Is there a way to do this
without VBA? If not, could someone help me write a code, as I am not very
experienced with VBA.

Thanks ever so much,
Julie
 
First, a little preaching, then the answer.
Keeping separate tables by month is a very common mistake. There is no
necessity for this method. All you really need is 2 additional fields in
your table, one for the month and one for the year.
Tables are to store data as efficiently as possible. Queries are to present
the data you want to see. If you want to work with one month at a time,
filter the query, form, or report. It is much easier than changing tables
and having to rewrite queries each month.

The only difference in your import routine, would be to link to the external
table rather than import it. Then use an append query with calculated fields
for the month and year to put the data in your live table.

And the Answer Is:
If you are not comfortable with VBA, open the query and modify it by hand
every month before you run it.
 
Back
Top