dynamic table name

[aftershokk]

Hi all, I am not that good with VBA but I am trying.

I want to make a backup copy of a table every month using a macro that inserts the current date and time into the name of the table. I tried using copy object as shown below but it just pastes the name as written?

Function Macro1()
On Error GoTo Macro1_Err

DoCmd.CopyObject "", "test & Date", acTable, "monthly_table"


Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

 

[KeithG]

Why not use a make table query?

 

[pbaldy]

Try

DoCmd.CopyObject "", "test" & Date(), acTable, "monthly_table"

 

[boblarson]

Change this part:

"test & Date"

to this:

"test" & Date


But I wouldn't leave slashes in the object names, so I would reduce it to this before doing it:

"test" & Year(date) & Month(Date) & Day(Date)

 

[pbaldy]

Good point Bob. You could also use:

Format(Date(), "yyyymmdd")

or whatever format you want.

 

[boblarson]

Good point Bob. You could also use:

Format(Date(), "yyyymmdd")

or whatever format you want.

Good point as well

 

[aftershokk]

as always

This forum is my life saver! Thanks to all!

I actually had something like Bob's originally but I messed up the quotes (which I always do!)

thanks!!!

 

[boblarson]

This forum is my life saver! Thanks to all!

I actually had something like Bob's originally but I messed up the quotes (which I always do!)

thanks!!!

Just as a reminder - for future reference - if you have variables or references to other objects, they need to be outside of the quotes. If you want something literally to be there, then you put it inside the quotes.