insert date into table name

  • Thread starter Thread starter Ypi
  • Start date Start date
Y

Ypi

I have a problem.
I need to make a macro that copies a table, renames it
and adds the current date into the tables name...
could someone give me any idea how to solve that problem?

thanks
 
Ypi said:
I have a problem.
I need to make a macro that copies a table, renames it
and adds the current date into the tables name...
could someone give me any idea how to solve that problem?

thanks
Click to expand...

It's easy enough to do, but on the face of it, it seems like a bad idea.
I can't think of a case in which it isn't a bad idea to incorporate
*data* into the name of an object. If you're going to have one table
just to hold data from, say, a particular date, and another table to
hold the same kind of data from another date, then it will be much
better to combine them into a single table with an additional field to
hold the date in question. That way, you don't have to create another
table each time you get data for another date, and you can process this
information with easy queries to extract the data for any given date.
Do you see what I'm getting at? And if you're doing this for archiving
purposes, why not just have a single archive table with a field for the
archive date, and use append queries to add data to that table, with the
archive date specified as a calculated field?

If you insist on doing what you originally asked, you can do it using
the TransferDatabase VBA method or macro action.
 
if you're entering similar data into different tables in
order to segregate the data by date, then you're making a
common mistake that will sabotage your db's effectiveness
and force you to keep reinventing the wheel over and over.
suggest you add a date field to your data table and set
the default value to Date(), which will automatically
populate the field with today's date every time you enter
a new record. viola! now access can group, segregate and
manipulate your data by date whenever you tell it to.
 
actually i'm doing that because i need to have an
overview... the table is about timber... and later i need
to have a report on which date i had something for a
special measure... there could be up to 6 possible
different tabels with the same date later on
and then i can get that kind of overview i need...

and i need to rename (and insert the date) the table
inside the same database
If you insist on doing what you originally asked, you can do it using
the TransferDatabase VBA method or macro action.
Click to expand...

i have tried to do it with a macro action, but can't
figure out how to insert the date with it.... what kind of
syntax i have to put into the "new name" to have it with a
date in the beginning of a table...??? could u please
explain???
 
actually i'm doing that because i need to have an
overview... the table is about timber... and later i need
to have a report on which date i had something for a
special measure... there could be up to 6 possible
different tabels with the same date later on
and then i can get that kind of overview i need...
Click to expand...

As Dirk and Tina say, you're making a very common mistake here: it
sounds like you feel that you must have a Table with the particular
data in order to generate a report for a particular date. This
assumption is INCORRECT.

You can, and should, base your Report on a Query which selects the
records for the date or date range from the table. It is not
necessary, and it is not good design, to store data (a date) in a
table name for this purpose! If you want an overview, *use Queries* to
get a flexible, user-controlled overview, rather than storing data in
an inflexible, hard to manage proliferation of tables.
 
ypi said:
actually i'm doing that because i need to have an
overview... the table is about timber... and later i need
to have a report on which date i had something for a
special measure... there could be up to 6 possible
different tabels with the same date later on
and then i can get that kind of overview i need...

and i need to rename (and insert the date) the table
inside the same database


i have tried to do it with a macro action, but can't
figure out how to insert the date with it.... what kind of
syntax i have to put into the "new name" to have it with a
date in the beginning of a table...??? could u please
explain???
Click to expand...

Your explanation makes me more sure than ever that you're trying to do
something you shouldn't do. Please reread the responses from Tina and
John Vinson. But if you insist on doing this, you can use a macro like
this:

Action: TransferDatabase
Transfer Type: Export
Database Type: Microsoft Access
Database Name: =[CurrentDb].[Name]
Object Type: Table
Source: Table1
Destination: ="Table1_" & Format(Date(),"yymmdd")
Structure Only: No

Personally, I'd use VBA instead of a macro. It's more flexible and
gives you error-handling, which you don't get with macros. That is, I'd
use VBA *if* I were going to do this -- but I wouldn't do it in the
first place.
 
I import web stats every 30 days as a csv files into a table, and run
queries on this to sort it by IP etc.

this month, 30 days stats in a csv translated into a 210Mb single table
database!

I only keep this one "data" table in the db and every time I import the
fresh data into it.

I then export the query results as tables to an archive database for
analysis using transfer database using a macro.

I name these tables using format to insert the date (ie.the example n
this thread) so I know when they are from.

I do this because if I was to use a query to search the db on date as
well, I'd have a massive db which would slow it right down!

Instead I archive the original csv files and the queried tables.

The obvious problem with this is if I want to re-run the queries on old
data it saves it with todays date...
But I can easily find and rename this.

Perhaps YPI is in a similar boat?
 
Back
Top