Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010

    Question Unanswered: removing passthru queries

    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?

    Function ClearPassThruQueries()
    '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!
    daoDB.QueryDefs.Delete (daoQDF.Name)
    'Refresh the list of remaining QueryDef objects
    End If



    ClearPassThruQueries = False
    MsgBox "ClearODBC encountered an unexpected error: " & Err.Description

    End Function

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    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
            End If
        Next qdf
        Set dbs = Nothing
        varList = Split(strList, ";")
        For i = 0 To UBound(varList)
            DoCmd.DeleteObject acQuery, varList(i)
        Next i
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    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?

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 15
    With a For Each... instruction, you loop into the items of a collection (here QueryDefs) while you change the contents of the same collection inside the loop, hence the problem.

    The technique I suggested works in 2 steps:
    1. Build a list of the items names.
    2. Use that list to delete each item.

    To unconditionally delete all Qerydef objects, you can also use:
    Sub DeleteAllQueries()
            If CurrentDb.QueryDefs.Count = 0 Then Exit Do
            DoCmd.DeleteObject acQuery, CurrentDb.QueryDefs(0).Name
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts