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