Delete a table

S

Shane Nation

I am trying to get a database to delte a table if the to dates dat exceeds a
set vaule.

I want this to happen when a button is clicked on a form, it then checks
todays date and if todays date exceeds the set vaule (i.e 10/07/2005) it
will delete a table called "Last week" before it re creates it.

That way I can be sure no "old" data exists.

Please does anyone know how to do this?

Thanks
 
D

Douglas J. Steele

The best way is not to do it at all.

Adding and deleting tables should be a rare occurence, not something you do
on a regular basis.

Make sure all of the records in your table have a date field on them (you
can add a field named, for instance, "CreatedDate" to your table and set its
default value to =Date(), and any new record added to your table will
automatically get datestamped)

Then, make sure that all of your relevant queries filter out records that
are older than you want. You do this by having something like WHERE
CreatedDate > DateAdd("w", -2, Date()) to filter out any records that were
created more than 2 weeks ago.
 
S

Shane Nation

Thanks for the advice, but when adding in excess of 120,000 records a week
to the table I really do want to delete it.

Shane
 
D

Douglas J. Steele

In that case, delete rows from the table, not the table itself.

To me, having a table named "Last Week" is a bad design. You're effectively
storing data in the name of the table, and you should never do that, anymore
than you should have fields in a table named Monday, Tuesday, Wednesday,
etc.

However, if you're determined to use tables in this way, you can delete the
table using code like:

If Date() > #07/10/2005# Then
CurrentDb.TableDefs.Delete "Last Week"
End If

A couple of comments about that code.

I see that you're in the UK, so I'm assuming that 10/07/2005 means 10 July,
2005 to you. Regardless of what your regional settings are, you must put the
date in mm/dd/yyyy format when using the # delimiters. (Other options are to
use unambiguous date formats, such as #10 Jul 2005#, or use the DateSerial
function: DateSerial(2005, 7, 10))

Also, I'm using DAO to do the actual deletion. If you're using Access 2000
or 2002, and you haven't already added a reference to DAO, you'll have to,
as out of the box, they don't include one. With any code module open, select
Tools | References from the menu bar, scroll through the list of available
references until you find the one for Microsoft DAO 3.6 Object Library, and
select it.
 
M

MacDermott

At the risk of once again providing an answer which is not what you think
you're looking for, I'd strongly suggest that you consider deleting all
records from your table, instead of actually deleting and re-creating the
table. One reason for my advice is that when you re-create the table, you'll
have to re-create all of its indexes and relationships.
Deleting all the records is easy - just write a Delete Query for that
table with no criteria. If you want to create a button to run the query,
the command button wizard offers this option.
 
S

Shane Nation

Thank you for your help - both of you. I agree that Last week is not a good
table name, but as I am working with highly confidential data I can not post
real names.

I will think very hard about deleting rows, not tables.

Thanks

Shane
 
L

Larry Linson

"Shane Nation"
Thank you for your help - both of you.
I agree that Last week is not a good
table name, but as I am working with
highly confidential data I can not post
real names.
Click to expand...

It's not the _name_ that concerns them, but the implication that you are
keeping "last week's" information in that table, so the table's identity is,
in fact, data.
I will think very hard about deleting
rows, not tables.
Click to expand...

Deleting either tables or rows, for that much data, is likely to mean that
you will have to Compact your database with some frequency. MVP Tony Toews'
has an example of using a temporary database to contain a temporary table.
You might adapt that technique to handle your data additions and
deletions -- even though it may not be, strictly speaking, a "temporary
table".

Larry Linson
Microsoft Access MVP
 
Top