Adding Date to the table name in a Make Table Query

C

Cathy

Before I delete the data in a particular table, I need to copy it into a
table with the prefix of today's date. Is there a way to create a make table
query that does this? Is there a better way? I've tried things like:

SELECT tblCO_Order_20.*
INTO Format(Now(),"yyyymmdd") & tblCO_Order_Level20
FROM tblCO_Order_20;

But no luck. I can't find a macro command to copy a table and save it with
a different name. What other options do I have?
 
F

fredg

Before I delete the data in a particular table, I need to copy it into a
table with the prefix of today's date. Is there a way to create a make table
query that does this? Is there a better way? I've tried things like:

SELECT tblCO_Order_20.*
INTO Format(Now(),"yyyymmdd") & tblCO_Order_Level20
FROM tblCO_Order_20;

But no luck. I can't find a macro command to copy a table and save it with
a different name. What other options do I have?
Click to expand...


As your Make Table query, use:

SELECT tblCO_Order_20.*
INTO ATempTableName
FROM tblCO_Order_20;

Then use VBA to change the table name to the one you want:

Public Sub ChangeTableName()
DoCmd.Rename Format(Date, "yyyymmdd") & "tblCO_Order_Level20",
acTable, "ATempTableName"
End Sub
 
O

Ofer Cohen

The Date need to be in the Select section

Try

SELECT tblCO_Order_20.* , Format(Now(),"yyyymmdd") AS TodayDate
INTO tblCO_Order_Level20
FROM tblCO_Order_20
 
Top