Changing destination of a Make Table Query

[gareth001]

Hi,

I've got a make table query which points to a certain location, but within VBA I want to be able to add a date/time stamp on the table name.

How can I change the new table name on the fly (I'm creating a table in a seperate database)?

I was origionally going to be using a select query and use TransferDatabase but this only gives me a link in the other database to where the table originated.

Thanks for any help in advance
Gareth

 

[gol4]

the change would be in your sql

SELECT Table1.field1, Now() AS [timestamp] INTO newtbl
FROM Table1;

 

[gareth001]

Thanks, but i'm after the table name being changed as the table is created, not data.

 

[gol4]

Sorry I could have posted a better example.

Dim strsql As String
strsql = "SELECT test1 INTO " & "[tablename" & Date & "] FROM Table1"
CurrentDb.Execute strsql