VBA to delete 200 Reports from MDB

[WANNABE]

I was given this code that would delete Queries starting with ~. And I thought
maybe I would be able to use it to delete 200 reports, but I obviously do not
understand this well enough, would someone please provide me some insight??

I looked at the MSysObjects table and figured that since all Reports are of TYPE
= -32764 maybe I could change this line
If VBA.InStr(Qdef.Name, "~") = 1 Then
to
If VBA.InStr(Qdef.TYPE, -32764) = 1 Then
I have discovered that Qdef.name does not represent the fieldname NAME ???????
Thank you to anyone who helps..

This is what I started with>>
Public Sub SQ1()

Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef
Dim i As Integer

Set Db = Access.CurrentDb
For i = Db.QueryDefs.Count - 1 To 0 Step -1
Set Qdef = Db.QueryDefs(i)
If VBA.InStr(Qdef.Name, "~") = 1 Then
Access.DoCmd.DeleteObject Access.AcObjectType.acQuery, Qdef.Name
End If
Set Qdef = Nothing
Next
Set Db = Nothing

End Sub

 

[Ken Snell \(MVP\)]

Assuming that you want to delete reports based on some identifier in the
report name:

Dim lngNumber As Long
For lngNumber = CurrentProject.AllReports.Count - 1 To 0 Step -1
If CurrentProject.AllReports(lngNumber).Name Like "*SomeTextString*"
Then _
DoCmd.DeleteObject acReport,
CurrentProject.AllReports(lngNumber).Name
Next lngNumber

 

[Pieter Wijnen]

You didn't get that from me, did you?
You have found out one important thing about MSysObjects, you can extract
all objects of a type by querying the Type field
two ways to delete all reports (Ken's will not work for '97):

Method 1:
Dim Db As DAO.Database
Dim Rs As DAO.Recordset

Set Db = Access.CurrentDb()
Set Rs = Db.OpenRecordset("SELECT Name FROM MSysObjects WHERE TYPE=-32764";
DAO.dbOpenSnapshot)
While Not Rs.EOF
Access.DoCmd.DeleteObject Access.AcObjectType.acReport, Rs.Fields(0).Value
Rs.MoveNext
Wend
Rs.Close : Set Rs = Nothing
Set Db = Nothing

Method 2:
Dim Db As DAO.Database
Dim Cnt As DAO.Container
Dim i As Integer

Set Db = Access.CurrentDb()
Set Cnt = Db.Containers("Reports")
For i = Cnt.Documents.Count -1 to 0 step -1
Access.DoCmd.DeleteObject
Access.AcObjectType.acReport,Cnt.Documents(i).Name
Next
Set Cnt = Nothing
set Db = Nothing

HtH

Pieter

 

[Guest]

I can not say for sure, but your name sounds familiar and it was just a week
or so ago, that you very much for all your help, I will try to test this
later today and post back.. Again Thank you, and I am sorry If I should have
aclnowledged your work better, managing these posts and replies through IE is
difficult and the office block Outlook Express...
Thanks again.

 

[John W. Vinson]

I was given this code that would delete Queries starting with ~. And I thought
maybe I would be able to use it to delete 200 reports, but I obviously do not
understand this well enough, would someone please provide me some insight??

If this is a one shot operation, you could open the Reports window and hit
Shift-Delete 200 times a lot quicker than coming up with a program to do it!

Obviously if it has to be done on multiple databases, the code is worthwhile,
and it sounds like you're on the right track now.

John W. Vinson [MVP]

 

[Guest]

This Worked Great, Thank you Ken

What do I need to do to better understand the parts of this code that elude
me?? Is there a VBA for Dummbies... I am able to semi-understand allot
of code that I read with very little VB or VBA background, but I want to
really understand and be able to write it... What do you suggest?? Books,
Web Sites???
=================================

 

[Guest]

Method 1 worked Great, Thank you Pieter

Question: Method 2 failed with
"Invalid Use of Property" on the
..acReport IN
Access.AcObjectType.acReport , Cnt.Documents(i).Name

What do I need to do to better understand the parts of this code that elude
me?? Is there a VBA for Dummbies... I am able to semi-understand allot
of code that I read with very little VB or VBA background, but I want to
really understand and be able to write it... What do you suggest?? Books,
Web Sites???
=================================

 

[Guest]

Thank you John for your comments and time!
My response is that I wish to serve 2 purposes
1, Yes this is something will need to be done more then once, during the
development of a database.
2, I am always looking to learn, and I appreciate everyone’s help immensely!!
================================

 

[Ken Snell \(MVP\)]

This Worked Great, Thank you Ken

What do I need to do to better understand the parts of this code that
elude
me?? Is there a VBA for Dummbies... I am able to semi-understand
allot
of code that I read with very little VB or VBA background, but I want to
really understand and be able to write it... What do you suggest??
Books,
Web Sites???

I have found books to be a good starting point for me when trying to learn
new programming languages. Most of the ACCESS books contain large quantities
of VBA programming code examples in them. See www.viescas.com for some book
recommendations.

Or go to your local bookstore and look through various ACCESS books -- at
least one of them will probably "jump out" at you as being a good source for
you to buy.

 

[Pieter Wijnen]

Method 2 should have no line-break before
Access.AcObjectType.acReport,Cnt.Documents(i).Name
Just wrapped by the news reader

Pieter

 

[Larry Linson]

"Programmer - wannaB" wrote

What do I need to do to better understand the parts
of this code that elude me?? Is there a VBA for Dummbies...

Actually, I think there is. I haven't reveiwed it, so can't comment on its
usefulness.

I am able to semi-understand a lot of code that I read
with very little VB or VBA background, but I want to
really understand and be able to write it... What do
you suggest?? Books, Web Sites???

Depending on your version of Access, I think you might benefit from one of
the following, both published by Wrox:

(1) Access 2003 VBA Programmer's Reference, by Cardoza, Hennig, Seach, &
Stein

(2) Access 2007 VBA Programmer's Reference, by Hennig, Cooper, Griffith, &
Stein

Much of what we do with VBA is to automate functions we could do manually in
Access, and many of the examples first explain the manual operation, and
then discuss writing VBA to automate it.

There's a list of website resources at the SharePoint Site for my Access
Special Interest Group, http://mail.ntpcug.org/accesssig/default.aspx. A
really good starting point for website reference is
http://www.mvps.org/access/, the FAQ site for the USENET newsgroup
comp.databases.ms-access.

Larry Linson
Microsoft Access MVP

 

[WANNABE]

Thank you very much !! I looked over the web sites and they look like great
resources
I will go to the book store and review the 2003 VBA book..
Thanks again..

"Programmer - wannaB" wrote

What do I need to do to better understand the parts
of this code that elude me?? Is there a VBA for Dummbies...

Actually, I think there is. I haven't reveiwed it, so can't comment on its
usefulness.

I am able to semi-understand a lot of code that I read
with very little VB or VBA background, but I want to
really understand and be able to write it... What do
you suggest?? Books, Web Sites???

Depending on your version of Access, I think you might benefit from one of
the following, both published by Wrox:

(1) Access 2003 VBA Programmer's Reference, by Cardoza, Hennig, Seach, &
Stein

(2) Access 2007 VBA Programmer's Reference, by Hennig, Cooper, Griffith, &
Stein

Much of what we do with VBA is to automate functions we could do manually in
Access, and many of the examples first explain the manual operation, and
then discuss writing VBA to automate it.

There's a list of website resources at the SharePoint Site for my Access
Special Interest Group, http://mail.ntpcug.org/accesssig/default.aspx. A
really good starting point for website reference is
http://www.mvps.org/access/, the FAQ site for the USENET newsgroup
comp.databases.ms-access.

Larry Linson
Microsoft Access MVP