I have this code that loops thru the querydef's and deletes the passthru queries, but it always leaves out a few. When I tried to trap an error, it does not give me the error description. It also does not finishing looping thru all the querydefs. Why does it not catch all of them?
'On Error Resume Next
On Error GoTo ClearODBC_Err
Dim daoDB As DAO.Database
Dim daoQDF As DAO.QueryDef
Set daoDB = CurrentDb()
cnt = daoDB.QueryDefs.Count
For Each daoQDF In daoDB.QueryDefs
'daoQDF.Type = 0 Are Local queries, 112 Are Passthru queries
If daoQDF.Name = "ToolSettings" Or daoQDF.Name = "DataList" Or daoQDF.Name = "Projects" Or daoQDF.Type = 0 Then
'Don't Delete these connections/queries.
'Name matched the naming spec, so zap it!
'Refresh the list of remaining QueryDef objects
Here's what I use. The criteria for keeping or deleting a querydef is different but can be easily adapted:
Public Sub DeleteAllQueries()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim varList As Variant
Dim strList As String
Dim i As Long
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
If Left(qdf.Name, 4) = "qry_" Then
If Len(strList) > 0 Then strList = strList & ";"
strList = strList & qdf.Name
Set dbs = Nothing
varList = Split(strList, ";")
For i = 0 To UBound(varList)
DoCmd.DeleteObject acQuery, varList(i)
The problem I am having is that in my looping, it only finds half of the passthru queries that are linked. Why is it not seeing the others? when I run it again then it finds 1 or 2 more and then deletes those. I don't understand why it does not see all of them when I initially run it?