Code or Macro maybe?

[Sean]

I am not sure what would be best or how to do it, so if anyone could assist,
or point me in the right direction, that would be great.

I have a database attached to an Excel spreadsheet. The spreadsheet is the
raw data for all the queries in the database. The spreadsheet is over
written each morning with new data. Everyday, I do a make table query and
save the data for historical reporting. How do I program a button on a form
or set up a macro to do this for me so I do not have to it manually everyday
where I am always changing the table name. I want the name of the table to
be Backlog with the current days date appened to it, so today would be
Backlog072709 and tomorrow's would be Backlog072809.
Thanks in advance,

 

[Jerry Whittle]

I highly recommend changing your file naming to a yyyymmdd format. That way
the table names will sort much better if you are looking for a specific date.

Put the following in a module. Change ASA to the correct name of the linked
spreadsheet. Put DoSQL on the On Click event of a command button. Make sure
to debug and save the module.

Public Sub DoSQL()

Dim SQL As String
Dim TheDate As String

TheDate = Format(Date, "yyyymmdd")

DoCmd.SetWarnings False
SQL = "SELECT * INTO " & _
"Backlog" & TheDate & _
" FROM ASA "

DoCmd.RunSQL SQL
DoCmd.SetWarnings True

End Sub

 

[Sean]

Thanks so much. I even played with it and got it to work using a query as
the raw data to make a table. Question, is there a bit of code I can use
that will rename a table 1st before I would run code like you sent me? So If
I have a table called PreviousDay" and I want to rename that table to
PreviousDay20090724, then run the code to create a new table previous day?