Deleting many forms or queries or reports

[jean]

Hi

I have a database containing aroud 100 form, 100 queries and 100
reports

Let say I want to delete all reports from one database an import all
reports from another database.

Right now, the only way to delete reports is one by one so I go Delete-
Entre, Delete-enter 100 times

When I want to import new records, I can go with Select all and bingo
they are imported easily.

Is there a command or something else that would delete All reports or
All Queries or All forms easily

thanks

 

[John Spencer]

You can use VBA code to do this. HERE is some OLD code (originally written in
Access 97) that might work for you.

BACKUP your database before using this. There is no UNDO.

Public Sub sDeleteAllForms()
Dim i As Long
Dim db As DAO.Database
Dim c As DAO.Container

Set db = CurrentDb()
Set c = db.Containers("Forms")
For i = c.Documents.Count - 1 To 0 Step -1
Debug.Print c.Documents(i).Name
DoCmd.DeleteObject acForm, c.Documents(i).Name
Next i

For i = CurrentProject.AllForms.Count - 1 To 0 Step -1
DoCmd.DeleteObject acForm, CurrentProject.AllForms(i).Name
Next i

End Sub

Same code to delete all reports in a database just change the line
Set c = db.Containers("Forms")
to
Set c = db.Containers("Reports")

and change
DoCmd.DeleteObject acForm, c.Documents(i).Name
to
DoCmd.DeleteObject acReport, c.Documents(i).Name

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Access Developer]

Get External Data will allow you to import from one to all of each kind of
object. Have you considered creating a new, empty database and importing the
objects you want from each of the two (or more) existing databases?

 

[David W. Fenton]

Public Sub sDeleteAllForms()
Dim i As Long
Dim db As DAO.Database
Dim c As DAO.Container

Set db = CurrentDb()
Set c = db.Containers("Forms")
For i = c.Documents.Count - 1 To 0 Step -1
Debug.Print c.Documents(i).Name
DoCmd.DeleteObject acForm, c.Documents(i).Name
Next i

For i = CurrentProject.AllForms.Count - 1 To 0 Step -1
DoCmd.DeleteObject acForm, CurrentProject.AllForms(i).Name
Next i

End Sub

Same code to delete all reports in a database just change the
line
Set c = db.Containers("Forms")
to
Set c = db.Containers("Reports")

and change
DoCmd.DeleteObject acForm, c.Documents(i).Name
to
DoCmd.DeleteObject acReport, c.Documents(i).Name

That can't be A97 code, as it uses CurrentProject.AllForms. In fact,
it seems to me you've got duplicate code, as the two counter loops
are indentical in result (the second won't have anything to do).

So, it seems to me your code would either be this:

Dim i As Long
Dim db As DAO.Database
Dim c As DAO.Container

Set db = CurrentDb()
Set c = db.Containers("Forms")
For i = c.Documents.Count - 1 To 0 Step -1
Debug.Print c.Documents(i).Name
DoCmd.DeleteObject acForm, c.Documents(i).Name
Next i
Set db = Nothing

Or it would be this:

Dim i As Long

For i = CurrentProject.AllForms.Count - 1 To 0 Step -1
DoCmd.DeleteObject acForm, CurrentProject.AllForms(i).Name
Next i

(I also have a problem using "i" as the variable name for as long
counter, but that's not really relevant here. It can be done
entirely without a counter, in fact:

Dim varItem As Variant

For Each varItem In CurrentProject.AllForms
DoCmd.DeleteObject acForm, varItem.Name
Next varItem

....while you still need the variable, you don't need to muck about
with the collection count)

 

[John Spencer]

Interesting I just tested the variations of that code and none of them seemed
to work when deleting forms.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

Interesting I just tested the variations of that code and none of
them seemed to work when deleting forms.

What error did you get?

 

[John Spencer]

In all variations of the code, I got

Error# 29068 : "Name of Database" cannot complete this operation. You must
stop the code and try again.

Access 2003
Calling routine from the VBA immediate window

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

In all variations of the code, I got

Error# 29068 : "Name of Database" cannot complete this operation.
You must stop the code and try again.

Access 2003
Calling routine from the VBA immediate window

Which line of code?

 

[John Spencer]

Any of the variations that use
DoCmd.DeleteObject acForm, c.Documents(i).Name


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

Any of the variations that use
DoCmd.DeleteObject acForm, c.Documents(i).Name

If you do Debug.Print c.Documents(i).Name (or whichever variation),
does that, too, cause the error?

If not, what about putting parens around c.Documents(i).Name, to
force evaluation (maybe there's a ByRef problem)?

 

[a a r o n . k e m p f]

I use stuff like this all the time to drop tables / queries a dozen at
a time
I do this in SQL Server of course

Just be careful with reading what you're executing

Select
'Drop Table ' + Name
From Sysobjects
Where xtype = 'U'
and name not like 'lkup%'

It's just a shame to me that Access doesn't allow you to multi-
select-- I think that being able to select 4 tables (using the shift
and ctrl buttons) and then hitting delete would just be a nice feature
to get in Access, for sho


Thanks

-Aaron