Rename a table with a dynamic date in name

[ericaann]

Is there a way to have a make table add today's date to the actual file name using a macro specifically?

I have a make table query that generates a table called "Permissions", in order to keep historical records (I need individual files) I have to go in and rename yesterday's ON HAND table to ON HAND 10-15, then run the make table query, so all my other queries reference the current ON HAND table.

What I would like to do is run a macro that would rename Permissions to Permissions 10-15 (today's date) and then run the make table that would create today's Permissions table.

Does that make sense?

Thanks,
Erica-ann

 

[Serious Sam]

Dynamic Date

Erica-Ann,

I made Macro1 in Access
First line was a Rename command
Second line was an Openquery command

Then I converted the macro to VBA code (Tools/Macro)
Then I added the Date = Now() reference
The final code is below, hope it helps
Sam

Function Macro1()
On Error GoTo Macro1_Err

Date = Now()
DoCmd.Rename "Permissions " & Date, acTable, "Permissions"
DoCmd.OpenQuery "Make Table Query", acNormal, acEdit

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function