ADOX Error 3265- "Item cannot be found..." | Newbie | | Join Date: Nov 2008
Posts: 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 -
Dim cat As New ADOX.Catalog
-
Dim cmd As New ADODB.Command
-
cat.ActiveConnection = CurrentProject.Connection
-
-
Set cmd = cat.Procedures("qryLostCostCodes").Command 'opens existing query to edit
-
cmd.CommandText = whr 'this should be your sql SELECT string
-
Set cat.Procedures("qryLostCostCodes").Command = cmd 'save query
-
cat.Views.Refresh
-
-
Set cat = Nothing
-
Set cmd = Nothing
-
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: - SELECT [costcode], [name]
-
FROM Site
-
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?
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: ADOX Error 3265- "Item cannot be found..."
Quote:
Originally Posted by charli  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
-
Dim cat As New ADOX.Catalog
-
Dim cmd As New ADODB.Command
-
cat.ActiveConnection = CurrentProject.Connection
-
-
Set cmd = cat.Procedures("qryLostCostCodes").Command 'opens existing query to edit
-
cmd.CommandText = whr 'this should be your sql SELECT string
-
Set cat.Procedures("qryLostCostCodes").Command = cmd 'save query
-
cat.Views.Refresh
-
-
Set cat = Nothing
-
Set cmd = Nothing
-
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:
SELECT [costcode], [name] FROM Site 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? Why not save yourself several lines of code as well as the External Reference: -
Dim qdf As DAO.QueryDef
-
-
Set qdf = CurrentDb.QueryDefs("qryLostCostCodes")
-
qdf.SQL = "Select * From Yada, Yada, Yada...;"
-
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: ADOX Error 3265- "Item cannot be found..."
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: ADOX Error 3265- "Item cannot be found..."
Quote:
Originally Posted by charli 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
-
Dim cat As New ADOX.Catalog
-
Dim cmd As New ADODB.Command
-
cat.ActiveConnection = CurrentProject.Connection
-
-
Set cmd = cat.Procedures("qryLostCostCodes").Command 'opens existing query to edit
-
cmd.CommandText = whr 'this should be your sql SELECT string
-
Set cat.Procedures("qryLostCostCodes").Command = cmd 'save query
-
cat.Views.Refresh
-
-
Set cat = Nothing
-
Set cmd = Nothing
-
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: - SELECT [costcode], [name]
-
FROM Site
-
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? Assuming you didn't like the Shortcut in Post #2, try this on for size, a slight change in syntax: - Dim cat As New ADOX.Catalog
-
Dim cmd As New ADODB.Command
-
-
cat.ActiveConnection = CurrentProject.Connection
-
-
Set cmd = cat.Views("qryLostCostCodes").Command
-
cmd.CommandText = "<Yada, Yada, Yada>;"
-
-
Set cat.Views("qryLostCostCodes").Command = cmd
-
cat.Views.Refresh
-
-
Set cat = Nothing
-
Set cmd = Nothing
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: ADOX Error 3265- "Item cannot be found..."
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?
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: ADOX Error 3265- "Item cannot be found..."
Quote:
Originally Posted by NeoPa 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? - Original Post Code Segment:
-
Set cmd = cat.Procedures("qryLostCostCodes").Command
-
cmd.CommandText = whr
-
Set cat.Procedures("qryLostCostCodes").Command = cmd
- My revised Code Segment:
- Set cmd = cat.Views("qryLostCostCodes").Command
-
cmd.CommandText = whr
-
Set cat.Views("qryLostCostCodes").Command = cmd
- Original Code will fail at the Assignment of the cmd Object Variable, namely:
- Set cmd = cat.Procedures("qryLostCostCodes").Command
- The revised Code will not fail at this Line when modified:
- Set cmd = cat.Views("qryLostCostCodes").Command
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: ADOX Error 3265- "Item cannot be found..."
Ah. My bad. Thanks for the explanation.
So you use .Views() where the original code used .Procedures. Nice :)
| | Newbie | | Join Date: Nov 2008
Posts: 23
| | | re: ADOX Error 3265- "Item cannot be found..."
Both answers work beautifully! I have no real problem with using DAO, but thank for explaining why the original version didn't work!
Charli
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: ADOX Error 3265- "Item cannot be found..."
Quote:
Originally Posted by charli Both answers work beautifully! I have no real problem with using DAO, but thank for explaining why the original version didn't work!
Charli Glad it all worked out for you, charli.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,384 network members.
|
