Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA to delete table contents from multiple DB's. (2000)

    I am looking for help in the creation of VBA code which will allow me to delete the contents of a specific table in multiple databases.

    Each database is identically structured - 5 similarly named tables, including the one for record deletion (TBACTUAL). Each database follows a naming convention of YMM###.MDB, where Y=year, MM=month and ###=unique identifier.

    How can I delete the table contents for TBACTUAL for up to 200 unique identifiers, for all months, spanning a number of years.

    The only alternative I can think of would be to hole up for days and daysand manually delete the records in greater than 4800 databases.

    Thanks for any help!!

    Rob.

    PS: I have a text file containing the absolute paths to each of the files which could be incorporated into a list/table to reference from the VBA code if necessary.
    PPS: The code can be in either Access 97 or 2000, but the source databases (where the table is to be cleaned out) is still in Access2

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: VBA to delete table contents from multiple DB's. (2000)

    You say you have a text file containing the paths to each of the files, do you mean .mdb files?
    If so it should be easy enough to execute the DELETE query in one of 2 ways.
    1.
    DELETE * FROM Tablename IN 'path and name of .mdb file'
    WHERE blah....
    or,
    2.
    Link to each database in turn and use a normal DELETE query.

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to delete table contents from multiple DB's. (2000)

    Thanks Pat.

    I think that option 1 will definitely work best for me.

    I have created the DELETE query, using the syntax below and this works fine for cleaning up a single DB.
    DELETE TBACTUAL.* FROM TBACTUAL IN "[FILE LOCATION]";

    How do I string together a multitude of these statements so as to clear many tables in a single go. I have modified my listing of DB file locations to include the full SQL statement so further changes should be simple. The format below did not work:
    DELETE TBACTUAL.* FROM TBACTUAL IN "[FILE LOCATION 1]";
    DELETE TBACTUAL.* FROM TBACTUAL IN "[FILE LOCATION 2]";

    Many thanks from an Access novice.

    Rob.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: VBA to delete table contents from multiple DB's. (2000)

    How are you using these DELETE queries?
    What you have to do is to loop thru your list of DELETE commands (probably best put into a table) and execute a DoCmd.RunSQL command.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to delete table contents from multiple DB's. (2000)

    Why are you using this kind of database structure in the first place? It is a maintenance nightmare!
    Charlotte

  6. #6
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to delete table contents from multiple DB's. (2000)

    Charlotte - Nightmare is an understatement. Any DB structure decisions are the sole responsibility of the software vendors (to whom we continually pay good money!!!). Meanwhhile, I sit here courageously trying to keep the machine running...

    Pat - Thanks for your help. I've gotten the process to work now. One click, a couple of minutes of churning and almost 5000 tables cleaned out. Sure beats the doing things manually, one by one, for days and days... Thanks again.

    Rob.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •