Trying to include Date function in new table name in SELECT INTO query

[JDubya]

Hi y'all

I'm trying to use a SQL SELECT INTO statement to back up the table tblContactImport into a new table with the same name albeit with today's date stuck on the end :- for example if I ran the query to back up tblContactImport today, then I would like the new table to be called tblContactImport_07/11/2016. I thought the syntax would be thus:-

SELECT * INTO "tblContactImport_" & Date()
FROM tblContactImport;

But I just get a "Syntax error in query. Incomplete query clause" message.

So I tried :-
SELECT * INTO ("tblContactImport_" & Date())
FROM tblContactImport;

and just got a different error message "the SELECT statement contains a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect".

Can anybody tell me whether what I wish to achieve is possible in an SQL query and if so what the correct syntax would be.

Ta very much.

 

[CJ_London]

in the context of a query, you cannot create it like that instead you will need to use vba to create and run the query. In addition date is actually a number with special format characteristics to display the number in a familiar way e.g.

clng(date()) will give you the date number

in vba to build your query you would do something like

dim sqlstr as string

sqlstr="SELECT * INTO tblContactImport_" & format(Date(),"yyyymmdd") & " FROM tblContactImport"
currentdb.execute sqlstr

note the date format - using / in table or field names should be avoided

 

[JDubya]

Hi CJ, thanks for that and I have taken note of your advice on table names! I was hoping to avoid the use of VBA as as I mentioned in a previous post which you helped me with, I'm just trying to do updates to data running SQL from within a Macro. I had already considered using the docmd.Copy Object command from within the Macro but couldn't work out how to solve naming the new table with today's date, which I knew I'd be able to do in VBA and call the code from the macro using the RunCode command. But to be fair this is all part of the learning process - finding out what can and cant be done in Macros before getting stuck into learning about VBA.

Cheers
John

 

[Minty]

You might also consider that without maintenance, you will end up with a lot of ever larger backups stored in your database. These might be better stored in a separate database that isn't your main one. They will probably be easier to manage and less likely to be corrupted/lost if they are stored elsewhere.

 

[JDubya]

Thanks for the advice Minty!
Cheers
John

 

[CJ_London]

I was hoping to avoid the use of VBA as as I mentioned in a previous post

you will find using macros are a) relatively limited compared to VBA, b) impossible to document and c) difficult to get help, partly because you cannot copy/paste your code and partly because most responders use vba.

If you look at the number of threads in macros there are current 3,709. For modules and VBA there are 46,718.

Macros are really only of use when developing web apps (which don't use VBA)

 

[MarkK]

Put the date in a field in a record, not in the name of the table....

INSERT INTO tblContactImportArchive
   ( Field1, Field2, InsertDate )
SELECT Field1, Field2, Date()
FROM tblContractImport

Then you can write a query called qryContactImport_161107 ....

SELECT * FROM tblContactImportArchive WHERE Date = #11/7/16#

Date information is data and belongs in a field in a table, not in the name of the table. That is why it is hard to put data into a table name in SQL, because data is not usefully stored in object names.

 

[JDubya]

Hi CJ, thanks for your advice and I accept that the general consensus from those in the know is to concentrate on VBA and don't waste too much time with macros (except when needed like autoexec). However, as you mentioned in your post Macros are required when developing web apps and at this very early stage of my Access career , I don't know if web apps are something that I will be getting involved in, so I thought it best to learn about macros just in case (although I don't intend spending too much time on this). Plus it means that when I move on to focusing on VBA, I'll have first hand experience of the limitations of Macros.
Cheers, John