ADOX Delete View Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a very annoying problem. Hopefully, the code below will make this more clear. Basically, I'm creating an ad hoc query from a form. With some help from this site, I learned how to temporarily save this query using ADOX Append commands. I then generate a crosstab query on this base query.

It works, but only once. I quickly figured out that I needed to delete the old queries before I could append ones with the same name again. But I can only delete one of them (i.e. either the base query or the crosstab, but not both). I get the following error: Run-time 3265: Item cannot be found in the collection corresponding to the requested name or ordinal

What's so frustrating is that it is so clearly there. I'm sure I've overlooked something, I I'll be honest i'm not sure exactly what all this is doing, but the concept seems pretty simple. If I delete the queries manually and REM out the delete commands it will work. But I need Access to do this on the fly. Thanks for any help. See code below.

Dim cat As New ADOX.Catalo
Dim cmd As New ADODB.Comman
Dim cmdx As New ADODB.Comman
Set cat.ActiveConnection = CurrentProject.Connectio

SQLstmt = "bunch of stuff for query

cat.Views.Delete "qryTmp"

cmd.CommandText = SQLstm
cat.Views.Append "qryTmp", cm

SQLcrosstab = "TRANSFORM Sum(qryTmp.bps) AS SumOfbps" &
" SELECT qryTmp.ticker, Sum(qryTmp.bps) AS [Total Of bps]" &
" FROM qryTmp" &
" GROUP BY qryTmp.ticker" &
" PIVOT Format(qryTmp.ddate," & "'Short Date')


cat.Views.Delete "qryTmpx

cmdx.CommandText = SQLcrossta

cat.Views.Append "qryTmpx", cmd

DoCmd.OpenQuery "qryTmpx

-------------------------------------------------------------------
 
My post received its due attention. DoCmd.DeleteObject is much more effective. Always learning.

----- Bob Ewers wrote: ----

I have a very annoying problem. Hopefully, the code below will make this more clear. Basically, I'm creating an ad hoc query from a form. With some help from this site, I learned how to temporarily save this query using ADOX Append commands. I then generate a crosstab query on this base query.

It works, but only once. I quickly figured out that I needed to delete the old queries before I could append ones with the same name again. But I can only delete one of them (i.e. either the base query or the crosstab, but not both). I get the following error: Run-time 3265: Item cannot be found in the collection corresponding to the requested name or ordinal

What's so frustrating is that it is so clearly there. I'm sure I've overlooked something, I I'll be honest i'm not sure exactly what all this is doing, but the concept seems pretty simple. If I delete the queries manually and REM out the delete commands it will work. But I need Access to do this on the fly. Thanks for any help. See code below.

Dim cat As New ADOX.Catalo
Dim cmd As New ADODB.Comman
Dim cmdx As New ADODB.Comman
Set cat.ActiveConnection = CurrentProject.Connectio

SQLstmt = "bunch of stuff for query

cat.Views.Delete "qryTmp"

cmd.CommandText = SQLstm
cat.Views.Append "qryTmp", cm

SQLcrosstab = "TRANSFORM Sum(qryTmp.bps) AS SumOfbps" &
" SELECT qryTmp.ticker, Sum(qryTmp.bps) AS [Total Of bps]" &
" FROM qryTmp" &
" GROUP BY qryTmp.ticker" &
" PIVOT Format(qryTmp.ddate," & "'Short Date')


cat.Views.Delete "qryTmpx

cmdx.CommandText = SQLcrossta

cat.Views.Append "qryTmpx", cmd

DoCmd.OpenQuery "qryTmpx


-------------------------------------------------------------------
 
Back
Top