VBA code to loop through all existing Microsoft Access 2003 queries and delete.

I'm looking for some VBA code that would loop and find all existing queries and reports in a database then delete them.  

The main reason why I want to do this is I have 50 databases that are extreme in size and after the deletion I plan to run VBA code to compact and repair.  

How will users still run their reports after this?  Generally I have a dialog box from which users can select reports from a table and now what I plan to do is import the selected report from a master report database.
LVL 1
stephenlecomptejrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aesmikeCommented:
The key to this is to iterate through the database's document collection.
However, I should tell you that if you have databases that are large in size, I doubt it's because of too many Queries and Reports.  Queries definitely don't take up too much space.  Reports don't either unless you have some embeded graphics.
I have a production app that contains over 100 reports and 75 queries.  The total size of the MDB is only 20Mb.  I'm thinking that your problem lies elsewhere.

First off, try to DECOMPILE one of these databases.  
1.  Fire up Access by choosing the Start Menu...Run... MSACCESS.EXE /DECOMPILE
2. Open the database
3. Compact the database
4. Check the size.
If you still have a size problem, it's likely one of the following:
A) Large local data tables
B) Forms or reports with embeded graphics.  For example, if you include a small, 1k JPG file as a graphic in one of your forms or reports, that 1k JPG gets converted to a BMP internally and will swell up the MDB by 1MB or more.  If you have enough of these, your MDB will get really Big.  To avoid all of this, make sure that your graphics are Linked to external files instead of embeded.

Check this out and let me know where you stand on this before taking the step of deleting/importing reports from a master MDB
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TextReportCommented:
I agree with aesmike that the most likely cause of the database bloat are the tables or forms rather than the queriew and reports.

On the queries you may have many temp queries that you want to remove, these names all begin with ~

Cheers, Andrew
Sub DeleteAllQueries()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
 
    Set db = CurrentDb()
    
    For Each qry In db.QueryDefs
        If qry.Name = "TESTDELETE" Then
           DoCmd.DeleteObject acQuery, qry.Name
           Debug.Print qry.Name & " Deleted"
        End If
    Next
    
End Sub

Open in new window

0
stephenlecomptejrAuthor Commented:
Thank you guys for your help.

You were right about the database starting off with size:  18,052 KB
I deleted all queries, forms, reports and modules and the size is now 12,292 KB.

Any documentation on how I can further reduce the size?
I know I have some fields as Text 255 - but a lot of these tables I need to keep!

Stephen
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

stephenlecomptejrAuthor Commented:
I know that I'm suppose to reward the first right answer but really helps is when someone posts the code that I need.  Thus I split the points between the two.  Thank you sincerely for both replies.  I do have one last question however...
0
aesmikeCommented:
well, first off, do you think that the size of the tables and indexes adds up to around 12Mb?
one way you can check this is to start with an empty mdb and import all the tables into it.  When done, check the MDB size.  Regarding your Text 255 fields, they're ok because Access only allocates space as needed in these fields.  IOW, a 255 Text ffield that has 5 characters takes up approximately 5 characters, not 255.
0
stephenlecomptejrAuthor Commented:
Hey aesmike and any others who may be interested,

I posted another question that pertains with aesmike's reply here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23118349.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.