Create Table with Date Appended

[Cathy]

How would I create a new table (tblNew), based on information in an existing
table (tblCurrentData), with today's date appended to the end of the tblNew
name? ( ie: tblNew06032010)

What I am trying to do is, I need a mechanism to store the last four days
worth of data so that comparisons can be made. I can then delete the tables
that are at least 5 days old.

 

[Daniel Pineault]

To directly answer your question, you can do it using the CopyObject Method

DoCmd.CopyObject(DestinationDatabase, NewName, SourceObjectType,
SourceObjectName)

However, the fact that you are doing this make me question your data
normalization. You should keep all your data, and the filter... as you need.
You shouldn't need to create copies of tables based on dates. This is bad!

I would reevaluate your table structure a little before continuing any
further. If you need help, post your current setup and I'm sure we can help
you find the proper setup to suit your needs.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Cathy]

Daniel,

The table is for accounting forecasting data. The users have a habit of
updating it several times, then change their mind and want to compare back to
something they had a couple of days ago.

I need to create versions so that they can do their job. How else would you
suggest accomplishing this?

I've tried your suggestion, and I can't seem to make " & Now()" work at the
end of the table name. How would you automate adding the date to the table
name using CopyObject?

Thank you so much for your help.

Cathy

 

[Daniel Pineault]

Your code would need to be something like something like

DoCmd.CopyObject , "tblNew" & Format(Date,"mmddyyyy"), acTable,
"tblCurrentData"
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.