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.

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" wrote:

> 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,

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" wrote:

> 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" wrote:
> 
> > 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.

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.



"Cathy" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > 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.