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

ADOX Error 3265- "Item cannot be found..."

P: 23
Error 3265, "Item cannot be found in the collection corresponding to the requested name or ordinal"

code programatically opens a query using ADOX and changed the sql

Expand|Select|Wrap|Line Numbers
  1. Dim cat As New ADOX.Catalog
  2. Dim cmd As New ADODB.Command
  3. cat.ActiveConnection = CurrentProject.Connection
  4.  
  5. Set cmd = cat.Procedures("qryLostCostCodes").Command 'opens existing query to edit
  6. cmd.CommandText = whr 'this should be your sql SELECT string
  7. Set cat.Procedures("qryLostCostCodes").Command = cmd 'save query
  8. cat.Views.Refresh
  9.  
  10. Set cat = Nothing
  11. Set cmd = Nothing
  12.  
debug says the error is on the line:
5. Set cmd = cat.Procedures("qryLostCostCodes").Command

so I was thinking maybe it couldn't find the query to open, but then if you alter it create a new query instead you get the error "Query already exists" (and I can see it exists)

the SQL is:
Expand|Select|Wrap|Line Numbers
  1. SELECT [costcode], [name]
  2. FROM Site
  3. WHERE costcode NOT IN ('B', 'A9000', 'I9000', 'I4000', 'I4300', 'I4200')
and costcode and name defintely exist... I even had this running at one point!

Any Ideas?
Jun 10 '09 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,679
@charli
Why not save yourself several lines of code as well as the External Reference:
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2.  
  3. Set qdf = CurrentDb.QueryDefs("qryLostCostCodes")
  4. qdf.SQL = "Select * From Yada, Yada, Yada...;"
  5.  
Jun 10 '09 #2

NeoPa
Expert Mod 15k+
P: 31,709
Looking through the Object Browser (F2) it seems that the Command property is a variant.

It may be worth checking that cat.Procedures("qryLostCostCodes").Command is an object of type ADODB.Command. I suspect there is a possibility of this being Null.

Unfortunately I don't have anything I can check this on so it will need to be you doing the checking.
Jun 10 '09 #3

ADezii
Expert 5K+
P: 8,679
@charli
Assuming you didn't like the Shortcut in Post #2, try this on for size, a slight change in syntax:
Expand|Select|Wrap|Line Numbers
  1. Dim cat As New ADOX.Catalog
  2. Dim cmd As New ADODB.Command
  3.  
  4. cat.ActiveConnection = CurrentProject.Connection
  5.  
  6. Set cmd = cat.Views("qryLostCostCodes").Command
  7. cmd.CommandText = "<Yada, Yada, Yada>;"
  8.  
  9. Set cat.Views("qryLostCostCodes").Command = cmd 
  10. cat.Views.Refresh
  11.  
  12. Set cat = Nothing
  13. Set cmd = Nothing
Jun 10 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
ADezii,

As your code is fundamentally the same up to the point where the OP reported the error (if I haven't misread somewhere) - is there any reason to assume this code will behave differently?
Jun 10 '09 #5

ADezii
Expert 5K+
P: 8,679
@NeoPa
  1. Original Post Code Segment:
    Expand|Select|Wrap|Line Numbers
    1. Set cmd = cat.Procedures("qryLostCostCodes").Command 
    2. cmd.CommandText = whr
    3. Set cat.Procedures("qryLostCostCodes").Command = cmd
  2. My revised Code Segment:
    Expand|Select|Wrap|Line Numbers
    1. Set cmd = cat.Views("qryLostCostCodes").Command 
    2. cmd.CommandText = whr
    3. Set cat.Views("qryLostCostCodes").Command = cmd
  3. Original Code will fail at the Assignment of the cmd Object Variable, namely:
    Expand|Select|Wrap|Line Numbers
    1. Set cmd = cat.Procedures("qryLostCostCodes").Command 
  4. The revised Code will not fail at this Line when modified:
    Expand|Select|Wrap|Line Numbers
    1. Set cmd = cat.Views("qryLostCostCodes").Command 
Jun 11 '09 #6

NeoPa
Expert Mod 15k+
P: 31,709
Ah. My bad. Thanks for the explanation.

So you use .Views() where the original code used .Procedures. Nice :)
Jun 11 '09 #7

P: 23
Both answers work beautifully! I have no real problem with using DAO, but thank for explaining why the original version didn't work!

Charli
Jun 11 '09 #8

ADezii
Expert 5K+
P: 8,679
@charli
Glad it all worked out for you, charli.
Jun 11 '09 #9

Post your reply

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