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

[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( "qryLostCostCod es").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?

[ADezii]

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( "qryLostCostCod es").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...;"

[NeoPa]

Looking through the Object Browser (F2) it seems that the Command property is a variant.

It may be worth checking that cat.Procedures( "qryLostCostCod es").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.

[ADezii]

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( "qryLostCostCod es").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

[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?

[ADezii]

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?

1. Original Post Code Segment:
   

   Set cmd = cat.Procedures("qryLostCostCodes").Command 
   cmd.CommandText = whr
   Set cat.Procedures("qryLostCostCodes").Command = cmd

2. My revised Code Segment:
   

   Set cmd = cat.Views("qryLostCostCodes").Command 
   cmd.CommandText = whr
   Set cat.Views("qryLostCostCodes").Command = cmd

3. Original Code will fail at the Assignment of the cmd Object Variable, namely:
   

   Set cmd = cat.Procedures("qryLostCostCodes").Command

4. The revised Code will not fail at this Line when modified:
   

   Set cmd = cat.Views("qryLostCostCodes").Command

[NeoPa]

Ah. My bad. Thanks for the explanation.

So you use .Views() where the original code used .Procedures. Nice :)

[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

[ADezii]

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.