select into with dynamic table

[boilergal]

Is it possible to create a select into statement with the into table being dynamic? I want the table name to be the last day of the previous month.

Here is what I am trying to do.
SELECT table.* INTO format(DateSerial(Year(Date()),Month(Date()),0),'yyyymmdd')
FROM table

 

[Joe4]

Welcome to the Board!

It is possible to do with VBA, but first you must ask yourself if it is really necessary. Seldom do you need to have two copies of the same database.
Can you explain why this is necessary? We may have other suggestions for you that may work better.

 

[boilergal]

I was afraid of that. Thanks for the quick response. The table is deleted and recreated every month which is why we make a copy of it. If it not our data so Access was the route decided to create the monthly backups.

 

[Joe4]

So, are you saying that you have a whole bunch of tables, one for each month in your database?
If so, that is probably not the most efficient structure.

What you probably want is one single table that you write your data to each month (so you are constantly adding to a single table, instead of creating new tables). If you do not already have some sort of date identifier, you could add a new field to your table that captures what month the data is for.

Then you could use a static Append Query to write your data to the table, and then an Update Query to update the date field (if you are doing this every month, it should be pretty easy - you would just be updating the records that have a blank in this field after you run your Append Query).

Does that sound like something that might work better for you?