removing/deleting passthru queries

[abenitez77]

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.
Else
'Name matched the naming spec, so zap it!
daoDB.QueryDefs.Delete (daoQDF.Name)
'Refresh the list of remaining QueryDef objects
daoDB.QueryDefs.Refresh
End If


Next


ClearODBC_Err:

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



End Function

 

[lameid]

my money is on refreshing the collection you are looping with for...

[red]'[/red]daoDB.QueryDefs.Refresh

 

[dhookom]

As per my posting in the Answers forum where I expect it was your question:
Every time you refresh your querydefs you are upsetting the "For Each".

Consider getting a querydef count and then looping through them from the end to the beginning. When you delete a querydef using this loop, it doesn't upset the collection.

Dim intQDCount as Integer
Dim intQDNum as Integer
intQDCount = daoDB.QueryDefs.Count
For intQDNum = intQDCount -1 to 0 Step -1
   ' your code here to delete

Next

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

Nice guess, I put the refresh after the End If and it still does the same thing.

 

[lameid]

I think you can simply remove the line but it may be one of those idiosyncratic things needed (I've never had a reason to try it out). You would have to put the line after the Next to keep it from interfering if that is the source but as Duane points out you could reconsruct the loop to avoid the problem and keep the line of code.

 

[dhookom]

I put the refresh after the End If and it still does the same thing" I would expect it to. Loop through the querydefs backwards and you should be ok.

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

ok and how do i access the name of the query if I change the loop ?
I tried daoQDF(x).Name and it didn't work.

 

[abenitez77]

That's what i meant to say...i put the refresh after the Next. sorry.

 

[lameid]

Duane's code suggestion will work.

 

[dhookom]

Try:

   daoQDF(intQDNum).Delete

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

daoQDF(intQDNum).Delete does not work.

 

[abenitez77]

This works, but ALL the queries don't come up in the loop.


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.
Else
'Name matched the naming spec, so zap it!
daoDB.QueryDefs.Delete (daoQDF.Name)
'Refresh the list of remaining QueryDef objects

End If


Next
daoDB.QueryDefs.Refresh

 

[lameid]

So putting all Duane's code suggestions together and doing some cleanup to retain the original test...

Function ClearPassThruQueries()
'On Error Resume Next
On Error GoTo ClearODBC_Err

Dim daoDB As DAO.Database
Dim daoQDF As DAO.QueryDef
Dim daoQDFs As DAO.QueryDefs
Dim intQDCount as Integer
Dim intQDNum as Integer

Set daoDB = CurrentDb()

Set daoQDFs = daoDB.Querydefs
intQDCount = daoQDFs.Count

For intQDNum = intQDCount -1 to 0 Step -1
   ' your code here to delete
   Set daoQDF = daoQDFs(intQDNum)
'Entire If statement should be tabbed over 
If daoQDF.Name = "ToolSettings" Or daoQDF.Name = "DataList" Or daoQDF.Name = "Projects" Or daoQDF.Type = 0 Then 'but fixing things in forum window isn't easy
    'Don't Delete these connections/queries.
Else
   'Name matched the naming spec, so zap it!
   daoQDF.Delete 

   'Refresh the list of remaining QueryDef objects
   daoQDFs.Refresh
End If

Next

 

[dhookom]

Thanks lameid.
I think my reply missed an "s" and should have been:

    daoQDFs(intQDNum).Delete

I don't think you need to refresh the querydefs in the loop.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I don't think you need to refresh the querydefs in the loop.

That is why I'm a little surprised the OP for loop doesn't work with that line out of the mix... but maybe it gets lost when it trys to step out of the object (I'm thinking it should keep track of an array position rather than the full object and keep cycling to other elements but that doesn't mean the Access programmers thought so).

But then again it's not like I make a practice of using collections I've removed children from... I don't remember ever trying it and if I did and it blows up, an alternate loop is obvious enough but I must say kudos on for loop with a step of -1, I probably would have used a less tidy While loop.

 

[dhookom]

I've written a few sections of code that loop through collections and delete items. It has always been my experience that looping through from end to beginning is the only reliable solution. I could be an issue that the querydefs collection is automatically refreshed. I have had the same issues with tabledefs.

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

lameid, thanks! that worked.

I only had to fix this line:
daoQDFs.Delete (daoQDF.Name)

 

[abenitez77]

That takes care of my passthru queries. How do I remove the linked tables? I tried the code below but it is not removing the links

Function DeleteAttachedTables()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim i As Integer

Set dbs = CurrentDb

For i = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(i)
'Delete all the existing DSN Connections
If (tdf.Attributes And dbAttachedODBC Or tdf.Attributes And dbAttachedTable) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next i


Set tdf = Nothing
dbs.Close
Set dbs = Nothing

End Function

 

[lameid]

I am not familiar with the attributes property off hand but it would seem that the word AND should be replaced with = in each case of your If statement.

 

[dhookom]

Learn to troubleshoot your code and how to use TGML to make your code easier to read. Try modify your code as below and then open the debug window (press Ctrl+G) to see the results. You should also set breakpoints to step through your code.

Function DeleteAttachedTables()
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim i As Integer

  Set dbs = CurrentDb

  For i = dbs.TableDefs.Count - 1 To 0 Step -1
    Set tdf = dbs.TableDefs(i)
    [COLOR=#4E9A06]'Delete all the existing DSN Connections[/color]
    debug.Print tdf.Name
    If (tdf.Attributes And dbAttachedODBC Or tdf.Attributes And dbAttachedTable) Then
      debug.print "Found one " & tdf.Name
      dbs.TableDefs.Delete (tdf.Name)
    End If
  Next i
  Set tdf = Nothing
  dbs.Close
  Set dbs = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP