Automate table export and rename

[Danu]

Input would be appreciated.

I need to export certain tables from one database to another database. This
needs to be done daily so, ideally, I would like each table to have the table
name plus the date.

Can a macro do this or code? I would like to this to take place when the db
closes. The database which contains the tables I want exported runs on a
scheduler.

 

[Klatuu]

Having a table for each day in a database is not a good idea. You would be
better off to have one table with a date field that separates the data by
date.
As to doing it when the database closes, you will have to have a form that
stays open at all times. You will put the code to do the transfer in the
Close event of that form.

 

[Danu]

So, just have an append query which would send the data out would be better?

Thank you!

- Danu

 

[Klatuu]

Exactly!
You will find this method makes any use of the data much easier. For
example, say you want to run a report based on the data and the user has to
put a date in. If they are all in different tables, you would have to put
code in the Open event of the report to select the correct table for the
record source, or modify the SQL of a query used as the report's record
source to use the correct table. Not too bad for a single date, but now,
lets say we want to include a range of dates. Oops, now we have a serious
problem.

If it is one table with a date field, all you need to do is provide the date
or date range to the OpenReport method. (See VBA Help for info on the Where
argument of the OpenReport method)