Date as table name in Make Table Query, SQL 1

[SellOut]

Hello,

I would like to run a Make Table Query that each time it is executed saves the new table as 'Table' followed by the date and time,
eg Table_2005_01_15_Time_06_37 or something similar in a different order.

Something along the lines of:

SELECT Original_Table.* INTO Table&Date&time
FROM Original_Table;

How can I use Date and Time as part of a table name in SQL?

Thanks

SO

 

[PHV]

How can I use Date and Time as part of a table name in SQL
I'm afraid you've to do that in VBA with, eg, DoCmd.RunSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mp9]

PHV is right, it can't be done in SQL. However, you don't have to delve into VBA as you can write a simple macro to do this instead.

First, create your query so that it creates a temporary table called tblTemp, i.e.

SELECT Original_Table.* INTO tblTemp
FROM Original_Table;

Then create a macro with two actions, thus:
1. OpenQuery:
Query Name: the name of the query you've just created
View: Datasheet
DataMode: Edit
2. Rename:
New Name: ="Table_" & Format(Now(),"yyyy_mm_dd""_Time_""hh_nn")
Object Type: Table
Old Name: tblTemp

Then all you have to do is run the macro whenever you want to generate the table - if you do this regularly you could even use the freeware Handy Access Launcher to automate running the macro for you at regular intervals (

http://www22.brinkster.com/accessory/resources/001.shtml

for more on HAL)

Note I used the Format() function to make the table name follow the example you gave in your post - you can tweak this to vary the table name. I'd use ="Table" & Format(Now(),"yyyymmddhhnnss") personally but at least this shows you what can be done.

Hope this helps.