Mass Delete

[News Groups]

using Access 2003. How can I delete *all* queries or all reports from a db?
It's easy to import using "select all" from another db, but how can I remove
all other than one by one?

Thanks.

John

 

[Guest]

Hi John,

While you can certainly write VBA code to delete all queries, all forms, all
macros, or all reports, etc., why not just create a new database and import
only the objects that you want? A major advantage of doing so is that you get
a fresh set of the normally hidden system tables (names start with MSYS),
which means that you are much less likely to suffer corruption problems.

With that said, here is code that I have for deleting all linked tables in
an Access application. This is useful when one makes a change to the back-end
database, because it's always best to delete table links and re-create them
in that case.

Option Compare Database
Option Explicit

Private Sub cmdDropLinkedTables_Click()
On Error GoTo ProcError

' How to Delete Multiple Tables Quickly
' http://support.microsoft.com/?id=210307

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim i As Integer

Set db = CurrentDb()

For i = db.TableDefs.Count - 1 To 0 Step -1
Set tdf = db.TableDefs(i)
If Len(tdf.Connect) > 0 Then
db.TableDefs.Delete tdf.Name
End If
Next i

RefreshDatabaseWindow
MsgBox "All linked tables have been dropped.", vbInformation, "Success..."

ExitProc:
'Cleanup
On Error Resume Next
Set tdf = Nothing
db.Close
Set db = Nothing
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description & _
" in procedure DropLinkedODBCTables.", vbCritical, _
"Error in cmdDropLinkedODBCTables_Click Event Procedure..."
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

using Access 2003. How can I delete *all* queries or all reports from a db?
It's easy to import using "select all" from another db, but how can I remove
all other than one by one?

Thanks.

John

 

[News Groups]

Thanks, Tom.

I usually do start from a new db and import, but occasionally need to keep
the db and eliminate a large group of queries or reports or ... So, good to
know how!

Thanks again.

John

 

[Dirk Goldgar]

using Access 2003. How can I delete *all* queries or all reports
from a db? It's easy to import using "select all" from another db,
but how can I remove all other than one by one?

Simplest might be to create a new database and import everything from
the old one except those queries or reports you don't want. That said,
here's a Sub you can use to delete all queries except, optionally, a
list of exceptions you want to preserve.

'----- start of code ----
Sub DeleteQueriesExcept(ParamArray Except() As Variant)

' Delete all queries except those specified in the argument list.

Dim strQueryName As String
Dim intDocX As Integer
Dim intExcluded As Integer
Dim blnDelete As Boolean

With CurrentData.AllQueries
For intDocX = .Count - 1 To 0 Step -1
strQueryName = .Item(intDocX).Name
blnDelete = True ' assume we'll delete this one
For intExcluded = 0 To UBound(Except)
If strQueryName = Except(intExcluded) Then
blnDelete = False
Exit For
End If
Next intExcluded
If blnDelete Then
DoCmd.DeleteObject acQuery, strQueryName
End If
Next intDocX
End With

Application.RefreshDatabaseWindow

End Sub
'----- end of code ----

You'd call it like this:

' delete all queries except Query1
DeleteQueriesExcept "Query1"

' delete all queries except Query1 and Query2
DeleteQueriesExcept "Query1", "Query2"

' delete all queries without exception
DeleteQueriesExcept


Something similar should work with reports (using
"CurrentProject.AllReports" and "acReport" instead of
"CurrentData.AllQueries" and "acQuery"), but you may run into trouble if
the reports have code behind them, since deleting them will affect the
database's VB project.

 

[Tony]

Tom & Dirk,

Don't know if either of you is still following this thread but if so, why
does the code loop through backwards using 'Step -1' vs. top-down? I've
been trying to accomplish the same thing John was with reports and noticed
that going top-down only deletes a subset of the objects then errors out.

Just curious as to why this happens...

Thanks & Ciao,

Tony

 

[John Vinson]

Tom & Dirk,

Don't know if either of you is still following this thread but if so, why
does the code loop through backwards using 'Step -1' vs. top-down? I've
been trying to accomplish the same thing John was with reports and noticed
that going top-down only deletes a subset of the objects then errors out.

Just curious as to why this happens...

Well, I'm not Tom or Dirk, but I'll jump in...

When you delete an object, Access renumbers all the remaining objects
in that class. E.g. if you delete query number 1, Access will renumber
query 2 to now be 1 (and 3 to 2, 4 to 3...); you then delete query 2,
and it renumbers again - so you still have a query 1 and a query 2,
which previously had different numbers.

John W. Vinson[MVP]

 

[Tony]

John,

Thanks for the reply and the excellent explanation; this info will be
helpful.

Ciao,

Tony