Daily Make Table

[Guest]

Hello,

I have a daily table that is generated everymorning. However, everyday I
have to open the query to add the a the date at the end of the table. Is
there a way to have access add the name and date to the table on a daily
basis so I can automate my process?

Example:
daily_servicing_20071022

My suggestion to my supervisor was to create one table and append to it
everyday and distinguish each day by adding a data_date field however it was
rejected.

 

[Douglas J. Steele]

Did your supervisor offer any reason for the rejection? What you've
suggested is the proper way to deal with it. Having multiple tables with the
name of the table containing metadata (i.e.: its name means something) is
definitely not a good idea.

However, if you're forced to, it's fairly straight-forward to replace the
SQL associated with a query:

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM Table1 INTO daily_servicing_" & Format(Date, "yyyymmdd")
Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

 

[Guest]

Apparently it would take to much disk space, we would have to change our
current reporting process, etc...

Thanks for the reply and I will see if i can figure this out. Looks like I
will have to build a module for it.