Scripting an Archive on multiple MDB files

[UKDisco]

Hi Guys,

We have about 11,000 MDB files that need to have some records that are older than 12 months archived out. I've run through the Guides and manually create an Append Query and Delete Query but this will obviously take some time to do every database manually. Is there an easy way to script he Queries so that we can just run a batch file that will open each file and run the queries and then close again??

Thanks for your help in advance.
Regards,
Dave

[barry07]

Presumambly there is a date field in each table that you use to determine which records are to be archived? If it has the same name in all tables then you can use the TableDef object to generate an Append and Delete query for each table.

If the date field is not named consistently you can still use this approach by identifying the TableDef field which has datatype = 8 (date). However, if there is more than one date field in the table it could be tricky.

HTH

[UKDisco]

Hi Barry,

Thanks for your reply and yes there is a date field thats used which I can manually create an Append and Delete Query but I need to be able to replicate this task to run over lots of different MDB files.

Unfortunately I'm a bit of a noob at Access, so any help is greatfully received.

[tgoodman]

UKDisco,

In order to handle such tasks for my own mdb databases, I put together a small utility project that I just got permission to release as an open source project. I have never tried it on 11,000 MDBs (about 100 is the limit I have tested).

Mult MDB SQL

This small SQL monitor executes sequential SQL commands in the open databases.

I have separate MDB database files for each of my clients, but the databases all have the same schema and share some common tables. This tool lets me work with all of them at once.

Tom

[puppydogbuddy]

you should look at these links for guidelines:

http://allenbrowne.com/ser-37.html

http://office.microsoft.com/en-us/access/HA010345681033.aspx

http://en.allexperts.com/q/Using-MS-Access-1440/Archive-Table-1.htm