macro to delete a query if it is present

[Roo Murray]

Hi

I used to have a macro that would delete a query (if it existed) and recreate the query with a new set of parameters

Does anyone know of a way of doing this in 2007? It seems to error all the time in this version:

'For i = 0 To MyDB.MyQuery.Count - 1
'If MyDB.MyQuery(i).Name Like "Name" Then

DoCmd.DeleteObject A_QUERY, "Name"

'End If
'Next i

MYFIELD = Forms![f_PARAM].[period]
MyField2 = Forms![f_PARAM].[last_period]
Set MYQUERY = MyDB.CreateQueryDef()
MYQUERY.Name = "Name"
MYQUERY.SQL = "SELECT DISTINCTROW LEDGER.ACCNO,LEDGER.new_acc, LEDGER.OPAC, LEDGER.COAC, LEDGER.PLAC,ledger.dept,ledger.div,ledger.chart,ledger.vantage, "
MYQUERY.SQL = MYQUERY.SQL & "val(nulltozero(LEDGER.[A" & MYFIELD & "])) AS act_ytd1, val(nulltozero(LEDGER.[b" & MYFIELD & "])) AS BUD_ytd1, val(nulltozero(LEDGER.[l" & MYFIELD & "])) AS LY_ytd1, "
MYQUERY.SQL = MYQUERY.SQL & "val(nulltozero([A" & MYFIELD & "]-val(nulltozero([A" & MyField2 & "])))) AS per_act1, "
MYQUERY.SQL = MYQUERY.SQL & "val(nulltozero([B" & MYFIELD & "]-val(nulltozero([B" & MyField2 & "])))) AS Per_bud1, "
MYQUERY.SQL = MYQUERY.SQL & "val(nulltozero([L" & MYFIELD & "]-val(nulltozero([L" & MyField2 & "])))) AS Per_ly1"
MYQUERY.SQL = MYQUERY.SQL & " FROM LEDGER ;"

MyDB.QueryDefs.Append MYQUERY

 

[ted.martin]

This seems to do the trick

Dim myQDF As QueryDef

Set myQDF = CurrentDb.QueryDefs("YourQueryName")


CurrentDb.QueryDefs.Delete myQDF.Name