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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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.
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.
ASKER
Hey aesmike and any others who may be interested,
I posted another question that pertains with aesmike's reply here:
https://www.experts-exchange.com/questions/23118349/Need-help-with-reducing-the-size-of-a-Microsoft-Access-2003-database.html
I posted another question that pertains with aesmike's reply here:
https://www.experts-exchange.com/questions/23118349/Need-help-with-reducing-the-size-of-a-Microsoft-Access-2003-database.html
ASKER
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