By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

cannot find action ou select distinct queries

P: 4

I have a function that produces some temporary queries. I create the queries instead of just executing from VB because I need one temp query to depend on another temp query. All the queries are named "rqt_tmp_something" and once I'm done with them I run this handy procedure to find and delete them.
This one query called "rqt_tmp_pop_grp_struc_distinct" doesn't delete.
I have noticed while watching my local variables that the action queries and select distinct don't show up in tables, view or procedures (I am using ADO).

Where are they!?!

Here is my not so handy dandy code.

Expand|Select|Wrap|Line Numbers
  1. Function delete_tmp_rqts() As Boolean
  3. '***************
  4. 'BUT: supprimer les rqts avec "tmp" dans leur nom
  5. 'INPUT: s/o
  6. 'OUTPUT: 0 if succès, -1 si échec
  7. 'MAJ: EJ, 21 mai 2012
  8. '***************
  10. On Error GoTo Err_delete_tmp_rqts:
  12.     Dim cat As ADOX.Catalog
  13.     Dim cmd As ADOX.View
  14.     Dim cnn As ADODB.Connection
  15.     Dim p_cmd As ADOX.Procedure
  16.     Dim tbl As ADOX.Table
  18.     'initialise catalog
  19.     Set cnn = CurrentProject.Connection
  20.     Set cat = New ADOX.Catalog
  21.     Set cat.ActiveConnection = cnn
  23.     'effacer les requetes select
  24.     For Each cmd In cat.Views
  25.         If InStr(cmd.Name, "rqt_tmp") Then cat.Views.Delete cmd.Name
  26.     Next cmd
  28.     'effacer les requetes d'action
  29.     For Each tbl In cat.Tables
  30.         If InStr(tbl.Name, "rqt_tmp") Then cat.Tables.Delete tbl.Name
  31.     Next tbl
  33.     For Each p_cmd In cat.Procedures
  34.         If InStr(p_cmd.Name, "rqt_tmp") Then cat.Views.Delete cmd.Name
  35.     Next p_cmd
  37.     delete_tmp_rqts = True
  39. Exit_delete_tmp_rqts:
  41.     Set cnn = Nothing
  42.     Set cat = Nothing
  44.     Exit Function
  46. Err_delete_tmp_rqts:
  48.     delete_tmp_rqts = False
  49.     Resume Exit_delete_tmp_rqts
  51. End Function
May 24 '12 #1
Share this Question
Share on Google+
2 Replies

P: 4
So it turns out that the action queries are in Catalog.Procedures at the very end of the list. I found a note here

"Note Using an object's ordinal position has become less important with the addition of the For Each…Next construct a few versions back. There are times, however, when you must loop through the entries in a collection. If your action changes the number of elements in the collection, using For Each…Next will, in general, fail. In cases when you're closing objects or deleting them from their collection, use a For…Next loop, using the objects' ordinal position to refer to them. Also, For Each…Next can only visit items in a collection from front to back. If you need to iterate through items in any other order (from back to front, for example), you'll need to use a normal For…Next loop instead."

It seems that my tmp query was being missed because of this. So the new code (i have abbreviated to the important part) goes :

Expand|Select|Wrap|Line Numbers
  1. effacer les requetes select
  2.     For i = 0 To cat.Views.Count - 1
  3.         If InStr(cat.Views(i).Name, "rqt_tmp") Then cat.Views.Delete cat.Views(i).Name
  4.     Next i
  6.     'effacer les requetes d'action
  7.     For i = 0 To cat.Tables.Count - 1
  8.         If InStr(cat.Tables(i).Name, "rqt_tmp") Then cat.Tables.Delete cat.Tables(i).Name
  9.     Next i
  11.     For i = 0 To cat.Procedures.Count - 1
  12.         If InStr(cat.Procedures(i).Name, "rqt_tmp") Then cat.Procedures.Delete cat.Procedures(i).Name
  13.     Next i 
May 28 '12 #2

Expert Mod 15k+
P: 31,491
Another way Emily, would be to process through the For Each Next loop simply noting the items to be removed, then remove them using the created list after the loop has finished. That way it should all work normally. It's certainly a good point to notice though, about removing items from a collection as you're processing through that same collection ;-)
Jun 2 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.