473,322 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

cannot find action ou select distinct queries

Hi.

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
  2.  
  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. '***************
  9.  
  10. On Error GoTo Err_delete_tmp_rqts:
  11.  
  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
  17.  
  18.     'initialise catalog
  19.     Set cnn = CurrentProject.Connection
  20.     Set cat = New ADOX.Catalog
  21.     Set cat.ActiveConnection = cnn
  22.  
  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
  27.  
  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
  32.  
  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
  36.  
  37.     delete_tmp_rqts = True
  38.  
  39. Exit_delete_tmp_rqts:
  40.  
  41.     Set cnn = Nothing
  42.     Set cat = Nothing
  43.  
  44.     Exit Function
  45.  
  46. Err_delete_tmp_rqts:
  47.  
  48.     delete_tmp_rqts = False
  49.     Resume Exit_delete_tmp_rqts
  50.  
  51. End Function
  52.  
May 24 '12 #1
2 1607
So it turns out that the action queries are in Catalog.Procedures at the very end of the list. I found a note here http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

"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
  5.  
  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
  10.  
  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
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

4
by: nc | last post by:
My iterator can find my collection when my Action class calls my jsp directly, however when my Action class calls an html file that is set up with IFrames (one of which is loading that same jsp), I...
4
by: Florian | last post by:
Hi, I have a table that contains log data, usually around a million records. The table has about 10 columns with various attributes of the logged data, nothing special. We're using SQL Server...
5
by: Martin Feuersteiner | last post by:
Dear Group I'm having trouble with the clause below. I would like to select only records with a distinct TransactionDate but somehow it still lists duplicates. I need to select the...
9
by: Kelvin | last post by:
Okay so this is baking my noodle. I want to select all the attritbutes/fields from a table but then to excluded any row in which a single attributes data has been duplicated. I.E. Here's my...
8
by: skinnybloke | last post by:
Hi - I have a problem with a memo field being truncated to about 255 characters when running a Access 2002 query. This only seems to happen if I use SELECT DISTINCT. It works ok using SELECT by...
18
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are...
15
by: MLH | last post by:
Mr Leigh Purvis gave me a very clever piece of SQL to accomplish what is probably an uncommon objective. In it, he uses the EXISTS operator. I can find no documentation on it in A97 HELP. I would...
4
by: benoit | last post by:
I have created a Typed DataSet that renders about 150 records. I put these into a datagrid that obviously shows me 150 records. My problem is now only one column is different most of the time....
8
by: cmrhema | last post by:
Hello I am preparing an application where I have different data stored in an array eg. Dim Vehicle(100) as String In this array different values are stored in an ordered manner. eg....
2
by: karinmorena | last post by:
I'm having 4 errors, I'm very new at this and I would appreciate your input. The error I get is: Week5MortgageGUI.java:151:cannot find symbol symbol: method allInterest(double,double,double)...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.