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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
aesmikeConnect With a Mentor Commented:
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
 
TextReportConnect With a Mentor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.